Received: from bast.unixathome.org (bast.unixathome.org [216.187.105.150]) by postgresql.org (Postfix) with ESMTP id 5741447510B for ; Mon, 1 Apr 2002 21:04:05 -0500 (EST) Received: from wocker (wocker.unixathome.org [192.168.0.99]) by bast.unixathome.org (Postfix) with ESMTP id 615D53F29; Mon, 1 Apr 2002 21:04:10 -0500 (EST) From: "Dan Langille" Organization: DVL Software Limited To: Tom Lane Date: Mon, 1 Apr 2002 21:04:12 -0500 MIME-Version: 1.0 Subject: Re: selecting records from a date range Reply-To: dan@langille.org Cc: pgsql-sql@postgresql.org References: <20020402003614.C631D3F29@bast.unixathome.org> In-reply-to: <19563.1017711824@sss.pgh.pa.us> X-mailer: Pegasus Mail for Windows (v4.01) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body Message-Id: <20020402020410.615D53F29@bast.unixathome.org> X-Archive-Number: 200204/9 X-Sequence-Number: 7274 On 1 Apr 2002 at 20:43, Tom Lane wrote: > "Dan Langille" writes: > > I'm having fun with this one. It was nice and fast before the vacuum, but > > very > > slow afterwards. The idea is to get all the records for a given day. > > [ scratches head... ] I tried to reproduce this, but for me it still > seems to prefer nestloop. Have you been playing any games with the > planner cost parameters? No, I haven't touched them. > What does pg_stats show for these tables? There are 9 rows for commit_log and 5 for commit_log_ports... If you want them I will extract them into a file and put them on a webpage. Well, does it help if I say it's back to being fast again? fp2migration=# fp2migration=# explain analyse fp2migration-# select commit_log.commit_date fp2migration-# from commit_log fp2migration-# where commit_date between ('2002-04-01'::timestamp + SystemTimeAdjust())::timestamp fp2migration-# and ('2002-04-01'::timestamp + SystemTimeAdjust() + INTERVAL '1 DAY')::timestamp fp2migration-# ORDER by commit_log.commit_date desc; NOTICE: QUERY PLAN: Index Scan Backward using commit_log_commit_date on commit_log (cost=0.00..6.19 rows=75 width=8) (actual time=0.28..1.95 rows=157 loops=1) Total runtime: 2.91 msec *shrug* Is there something happening in the background? I just tried doing another full analyze, but I was unable to reproduce the problem. Is there anything I can provide which might help? -- Dan Langille The FreeBSD Diary - http://freebsddiary.org/ - practical examples