X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 1724ED1B4FC for ; Wed, 17 Sep 2003 22:40:23 +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 13295-05 for ; Wed, 17 Sep 2003 19:40:12 -0300 (ADT) Received: from davinci.ethosmedia.com (unknown [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id B25B4D1B502 for ; Wed, 17 Sep 2003 19:40:09 -0300 (ADT) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3651667; Wed, 17 Sep 2003 15:40:42 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Rhaoni Chiu Pereira , PostgreSQL Performance Subject: Re: How to force an Index ? Date: Wed, 17 Sep 2003 15:38:33 -0700 User-Agent: KMail/1.4.3 References: <1063837067.3f68dd8b5a62b@sistemica.info> In-Reply-To: <1063837067.3f68dd8b5a62b@sistemica.info> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200309171538.33515.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/254 X-Sequence-Number: 3620 Rhaoni, First off, thanks for posting such complete info. > ... gsames00.ano_mes =3D to_char(ftnfco00.data_emissao,'YYYYMM') AND .= .. >=20 > ftnfco00.data_emissao is a timestamp. When I run the explain analyze i= t=20 says: >=20 > ... > -> Seq Scan on gsames00 (cost=3D100000000.00..100000006.72 rows=3D372= =20 width=3D10)=20 > (actual time=3D0.01..0.96 rows=3D372 loops=3D19923) > ... Your problem is that you're comparing against a calculated expression based= on=20 ftnfco00, which is being filtered in about 18 other ways. As a result, the= =20 planner doesn't know what to estimate (see the cost estimate of 100000000,= =20 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= =20 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)); --=20 -Josh Berkus Aglio Database Solutions San Francisco