X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 16CB5D1B518; Thu, 18 Sep 2003 14:45:41 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 81643-01; Thu, 18 Sep 2003 11:45:32 -0300 (ADT) Received: from host2.hostseguro.com (unknown [200.198.188.133]) by svr1.postgresql.org (Postfix) with ESMTP id C6302D1B513; Thu, 18 Sep 2003 11:45:27 -0300 (ADT) Received: from cpanel by host2.hostseguro.com with local (Exim 4.20) id 1A001t-0002hk-T3; Thu, 18 Sep 2003 11:45:09 -0300 Received: from 200.180.173.173 ([200.180.173.173]) by sistemica.info (IMP) with HTTP for ; Thu, 18 Sep 2003 11:45:09 -0300 Message-ID: <1063896309.3f69c4f5cd16a@sistemica.info> Date: Thu, 18 Sep 2003 11:45:09 -0300 From: Rhaoni Chiu Pereira To: josh@agliodbs.com Cc: PostgreSQL ADMIN , PostgreSQL Performance Subject: Re: [PERFORM] How to force an Index ? References: <1063837067.3f68dd8b5a62b@sistemica.info> <200309171538.33515.josh@agliodbs.com> In-Reply-To: <200309171538.33515.josh@agliodbs.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit User-Agent: Internet Messaging Program (IMP) 3.2.1 X-Originating-IP: 200.180.173.173 X-MailScanner-Information: Verificado pelo McAfee VirusScan / Scanned by McAfee VirusScan X-MailScanner: Nao infectado / Found to be clean X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - host2.hostseguro.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [32001 32001] / [47 12] X-AntiAbuse: Sender Address Domain - sistemica.info X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/308 X-Sequence-Number: 10396 I solve this problem doing this: create function date_to_yyyymm( timestamp ) returns gsames00.ano_mes%type as 'select to_char($1, ''YYYYMM''); ' language sql immutable strict; And changing the SQL where clause: ... gsames00.ano_mes = to_char(ftnfco00.data_emissao,'YYYYMM') AND ... to: ... gsames00.ano_mes = date_to_yyyymm(ftnfco00.data_emissao) AND ... Then it uses the gsames00 index instead of a SeqScan 'cuz it is camparing same data type, but .. I don't want to create this function 'cuz this aplication is used with Oracle too. I need to know if there is a way to set the to_char output to varchar instead of text ! Any Idea ? So, this way I wont have to change my aplication source. Atenciosamente, Rhaoni Chiu Pereira Sistêmica Computadores Visite-nos na Web: http://sistemica.info Fone/Fax : +55 51 3328 1122 Citando Josh Berkus : <> Rhaoni, <> <> First off, thanks for posting such complete info. <> <> > ... gsames00.ano_mes = to_char(ftnfco00.data_emissao,'YYYYMM') AND ... <> > <> > ftnfco00.data_emissao is a timestamp. When I run the explain analyze it <> <> says: <> > <> > ... <> > -> Seq Scan on gsames00 (cost=100000000.00..100000006.72 rows=372 <> width=10) <> > (actual time=0.01..0.96 rows=372 loops=19923) <> > ... <> <> Your problem is that you're comparing against a calculated expression based <> on <> ftnfco00, which is being filtered in about 18 other ways. As a result, the <> <> planner doesn't know what to estimate (see the cost estimate of 100000000, <> which is a "blind guess" values) and goes for a seq scan. <> <> Can I ask you to try this workaround, to create an expressional index on <> ftnfco00 (assuming that data_emmisao is of type DATE) <> <> create function date_to_yyyymm( date ) returns text as <> 'select to_char($1, ''YYYYMM''); <> ' language sql immutable strict; <> <> create index idx_data_yyyymm on ftnfco00(date_to_yyyymm(data_emmisao)); <> <> -- <> -Josh Berkus <> Aglio Database Solutions <> San Francisco <> <> <> ---------------------------(end of broadcast)--------------------------- <> TIP 4: Don't 'kill -9' the postmaster <>