pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (4+ messages)  latest in thread

Message-ID: <20020402003614.C631D3F29@bast.unixathome.org>
Permalink:  ../20020402003614.C631D3F29@bast.unixathome.org/
Also on:    postgresql.org/message-id/20020402003614.C631D3F29@bast.unixathome.org

 · 

reply

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