X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id ADCD09F946D; Sat, 20 May 2006 23:26:37 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 67252-06; Sat, 20 May 2006 23:26:36 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- X-Greylist: domain auto-whitelisted by SQLgrey- Received: from hub.org (hub.org [200.46.204.220]) by postgresql.org (Postfix) with ESMTP id 29C099F9316; Sat, 20 May 2006 23:26:37 -0300 (ADT) Received: from localhost (mx1.hub.org [200.46.208.251]) by hub.org (Postfix) with ESMTP id 86027290C35; Sat, 20 May 2006 23:26:30 -0300 (ADT) Received: from hub.org ([200.46.204.220]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 70109-07; Sat, 20 May 2006 23:26:33 -0300 (ADT) Received: from ganymede.hub.org (blk-7-151-244.eastlink.ca [71.7.151.244]) by hub.org (Postfix) with ESMTP id B9400290C1F; Sat, 20 May 2006 23:26:26 -0300 (ADT) Received: by ganymede.hub.org (Postfix, from userid 1000) id 0958061797; Sat, 20 May 2006 23:26:38 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id 07C0761736; Sat, 20 May 2006 23:26:38 -0300 (ADT) Date: Sat, 20 May 2006 23:26:37 -0300 (ADT) From: "Marc G. Fournier" X-X-Sender: scrappy@ganymede.hub.org To: Michael Glaesemann cc: "Marc G. Fournier" , pgsql-sql@postgresql.org Subject: Re: timestamp query doesn't use an index ... In-Reply-To: Message-ID: <20060520232250.R1114@ganymede.hub.org> References: <20060520223704.T1114@ganymede.hub.org> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/160 X-Sequence-Number: 25191 On Sun, 21 May 2006, Michael Glaesemann wrote: > > On May 21, 2006, at 10:42 , Marc G. Fournier wrote: > >> -> Seq Scan on page_schedule ps2 (cost=0.00..2364.95 rows=33110 >> width=16) (actual time=0.021..623.363 rows=94798 loops=1) > > I don't know about rewriting the query, but it appears your statistics are a > little out of date (e.g., rows expected/actual 33110/94798). Does running > ANALYZE help? the data is idle, just loaded it on my desktop for testing purposes ... being paranoid, I have been doing a vacuum analyze on the table as I change the index's *just in case*, but, doing a full analyze on the whole database doesn't change the results any: Actually, the above results are odd anyway, since a second run of the exact same query, shows more normal numbers: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=3051.91..3054.19 rows=183 width=16) (actual time=1030.970..1031.257 rows=128 loops=1) -> Seq Scan on page_schedule ps2 (cost=0.00..2364.95 rows=91594 width=16) (actual time=0.019..636.599 rows=94798 loops=1) Filter: (timezone('MST7MDT'::text, start_time) <= '2006-05-17 08:09:18'::timestamp without time zone) Total runtime: 1031.681 ms (4 rows) So not 100% certain where the 33110/94798 gap came from ;) ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . scrappy@hub.org MSN . scrappy@hub.org Yahoo . yscrappy Skype: hub.org ICQ . 7615664