Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 990C664FF9E for ; Thu, 7 Aug 2008 15:42:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 44212-09 for ; Thu, 7 Aug 2008 15:42:29 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from asmtpout016.mac.com (asmtpout016.mac.com [17.148.16.91]) by postgresql.org (Postfix) with ESMTP id 3E83D64FF96 for ; Thu, 7 Aug 2008 15:42:31 -0300 (ADT) MIME-version: 1.0 Content-type: text/plain; charset=WINDOWS-1252; format=flowed; delsp=yes Received: from alichino.refactor.it ([88.149.177.167]) by asmtp016.mac.com (Sun Java(tm) System Messaging Server 6.3-6.03 (built Mar 14 2008; 32bit)) with ESMTPSA id <0K58005W5UMHZW90@asmtp016.mac.com> for pgsql-performance@postgresql.org; Thu, 07 Aug 2008 11:42:21 -0700 (PDT) Message-id: <6DF1C81C-2663-4F50-9F25-F53D427DD206@mac.com> From: Giorgio Valoti To: pgsql-performance@postgresql.org In-reply-to: <489AEC42.5080007@archonet.com> Content-transfer-encoding: quoted-printable Subject: Re: Query Plan choice with timestamps Date: Thu, 07 Aug 2008 20:42:19 +0200 References: <321506D5-4400-470C-909E-C7700D49A540@mac.com> <489AB3EB.8040903@archonet.com> <15821866-9564-40E8-BC12-52ADD6D6EEA5@mac.com> <489AEC42.5080007@archonet.com> X-Mailer: Apple Mail (2.928.1) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200808/48 X-Sequence-Number: 30929 On 07/ago/08, at 14:36, Richard Huxton wrote: > Giorgio Valoti wrote: >> On 07/ago/08, at 10:35, Richard Huxton wrote: >>> Giorgio Valoti wrote: >>>> Hi, I have a timestamptz field that I want to use with a query, =20 >>>> but I don=92t need the full timestamp resolution, so I=92ve created = a =20 >>>> day_trunc(timestamptz) immutable function which I=92ll use with the = =20 >>>> query and with a new index: >>>> logs=3D> create index test_idx on blackbox (day_trunc(ts)); >>>> However, the query plan doesn=92t use the index: >>> >>> Does it use it ever? e.g. with >>> SELECT * FROM blackbox WHERE day_trunk(ts) =3D '...' >> It=92s used: > [snip] > > OK - so the index is working. > > If you disable seq-scans before running the query, does it use it =20 > then? > > SET enable_seqscan =3D off; Yes > [=85] > > In particular: > 1. Is the estimated cost more or less than 119773.92? QUERY PLAN = --------------------------------------------------------------------------= --------------- GroupAggregate (cost=3D0.00..122309.32 rows=3D74226 width=3D8) -> Index Scan using date_idx on blackbox (cost=3D0.00..101586.31 =20= rows=3D247736 width=3D8) > > 2. How does that match the actual time taken? QUERY =20= PLAN = --------------------------------------------------------------------------= ----------------------------------------------------------------- GroupAggregate (cost=3D0.00..122309.32 rows=3D74226 width=3D8) = (actual =20 time=3D0.222..1931.651 rows=3D428 loops=3D1) -> Index Scan using date_idx on blackbox (cost=3D0.00..101586.31 =20= rows=3D247736 width=3D8) (actual time=3D0.072..1861.367 rows=3D247736 = loops=3D1) Total runtime: 1931.782 ms But I haven=92t revised the vacuum settings. Thank you -- Giorgio Valoti