Re: selecting records from a date range

On 1 Apr 2002 at 20:43, Tom Lane wrote:

> "Dan Langille" <dan(at)langille(dot)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