From: Dan Langille <dan@langille.org>
To: pgsql-sql@postgresql.org
Subject: selecting records from a date range
Date: Mon, 1 Apr 2002 19:36:17 -0500
Message-ID: <20020402003614.C631D3F29@bast.unixathome.org> (raw)
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
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
Subject: Re: selecting records from a date range
In-Reply-To: <20020402003614.C631D3F29@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