Thread: selecting records from a date range
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.
Any clues?
background: Eventually, we'll be doing a INTERVAL addition to compensate
for time zone differences.
fp2migration=# explain analyse
fp2migration-# select commit_log.commit_date
fp2migration-# from commit_log, commit_log_ports
fp2migration-# where commit_log.commit_date between ('2002-04-
01'::timestamp)
fp2migration-# and ('2002-04-01'::timestamp +
INTERVAL '1 DAY')
fp2migration-# and commit_log.id = commit_log_ports.commit_log_id
fp2migration-# ORDER by commit_log.commit_date desc;
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..20.67 rows=1 width=16) (actual time=0.69..7.77
rows=52 loops=1) -> Index Scan Backward using commit_log_commit_date on commit_log
(cost=0.00..3.08 rows=1 width=12) (actual time=0.31..2.34 rows=163
loops=1) -> Index Scan using commit_log_ports_pkey on commit_log_ports
(cost=0.00..17.53 rows=5 width=4) (actual time=0.02..0.02 rows=0
loops=163)
Total runtime: 8.27 msec
But after:
Sort (cost=896.36..896.36 rows=134 width=16) (actual time=593.61..593.86
rows=52 loops=1) -> Hash Join (cost=7.89..891.61 rows=134 width=16) (actual
time=572.51..586.82 rows=52 loops=1) -> Seq Scan on commit_log_ports (cost=0.00..676.36 rows=41136
width=4) (actual time=0.09..280.45 rows=41136 loops=1) -> Hash (cost=7.66..7.66 rows=91 width=12) (actual
time=76.37..76.37 rows=0 loops=1) -> Index Scan Backward using commit_log_commit_date on
commit_log (cost=0.00..7.66 rows=91 width=12) (actual time=39.75..75.28
rows=163 loops=1)
Total runtime: 594.39 msec
OUCH.. That's terrible.
The two tables have 27981 and 41136 rows.
The result set is 47 rows
thanks folks.
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples
"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? What does pg_stats show for these tables?
regards, tom lane
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
"Dan Langille" <dan@langille.org> writes:
> Is there something happening in the background?
Not unless you've got a cron job running vacuums and/or analyzes ...
regards, tom lane