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
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