pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jason Earl <jason.earl@simplot.com>
Cc: Jean-Christophe Boggio <cat@thefreecat.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Optimization with dates
Date: Tue, 13 Nov 2001 21:20:34 -0500
Message-ID: <4513.1005704434@sss.pgh.pa.us> (raw)
In-Reply-To: <87g07iji5r.fsf@npa01zz001.simplot.com>
References: <72319017913.20011114005656@thefreecat.org>
	<87g07iji5r.fsf@npa01zz001.simplot.com>

Jason Earl <jason.earl@simplot.com> writes:
> I have a similar table (~16M rows) with an indexed timestamp, and have
> had similar problems.  I have found that even when I am using a
> constant timestamp like in this query.

> SELECT * FROM caseweights1 WHERE dt > '2001-11-01';

> I start getting sequential scans with 7.1.3 long before they are
> faster than index based queries.

Just out of curiosity, do the indexed timestamps correlate closely to
the physical order of the table?  I'd expect that to happen if you
are timestamping records by insertion time and there are few or no
updates.

7.1 and before assume that the index order is random with respect to
the physical order, which is a good conservative assumption ... but it
results in drastically overestimating the cost of an indexscan when
strong correlation exists.  7.2 keeps some statistics about ordering
correlation, and may perhaps do better with this sort of situation.
(I have no doubt that its estimates will need further tweaking, but
at least the effect is somewhat accounted for now.)

			regards, tom lane



view thread (11+ messages)  latest in thread

Message-ID: <4513.1005704434@sss.pgh.pa.us>
Permalink:  ../4513.1005704434@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/4513.1005704434@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, jason.earl@simplot.com, cat@thefreecat.org
  Subject: Re: Optimization with dates
  In-Reply-To: <4513.1005704434@sss.pgh.pa.us>

* 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