From: Dan Langille <dan@langille.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: selecting records from a date range
Date: Mon, 1 Apr 2002 21:04:12 -0500
Message-ID: <20020402020410.615D53F29@bast.unixathome.org> (raw)
In-Reply-To: <19563.1017711824@sss.pgh.pa.us>
References: <20020402003614.C631D3F29@bast.unixathome.org>
On 1 Apr 2002 at 20:43, Tom Lane wrote:
> "Dan Langille" <dan@langille.org> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dan@langille.org, tgl@sss.pgh.pa.us
Subject: Re: selecting records from a date range
In-Reply-To: <20020402020410.615D53F29@bast.unixathome.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox