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