Re: selecting records from a date range

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