pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dan Langille <dan@langille.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: selecting records from a date range
Date: Mon, 1 Apr 2002 21:04:12 -0500
Message-ID: <20020402020410.615D53F29@bast.unixathome.org> (raw)
In-Reply-To: <19563.1017711824@sss.pgh.pa.us>
References: <20020402003614.C631D3F29@bast.unixathome.org>

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




view thread (4+ messages)  latest in thread

Message-ID: <20020402020410.615D53F29@bast.unixathome.org>
Permalink:  ../20020402020410.615D53F29@bast.unixathome.org/
Also on:    postgresql.org/message-id/20020402020410.615D53F29@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, tgl@sss.pgh.pa.us
  Subject: Re: selecting records from a date range
  In-Reply-To: <20020402020410.615D53F29@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