On Tue, 5 Aug 2003 15:26:09 -0700, Sean Chittenden
<sean@chittenden.org> wrote:
>> I have an experimental patch lying around somewhere that tries to
>> work around these problems by offering different estimation methods
>> for index scans. If you are interested, I'll dig it out.
>
>Sure, I'll take a gander... had my head in enough Knuth recently to
>even hopefully have some kind of a useful response to the patch.
Sean, the patch is at http://www.pivot.at/pg/16-correlation-732.diff.
A short description of its usage can be found at
http://archives.postgresql.org/pgsql-performance/2002-11/msg00256.php.
If you are interested how the different interpolation methods work,
read the source - it shouldn't be too hard to find.
You might also want to read the thread starting at
http://archives.postgresql.org/pgsql-hackers/2002-10/msg00072.php.
>> does this mean that sensorid, evtime, and action are not
>> independent?
>
>Hrm... sensorid is sequence and grows proportional with evtime,
>obviously.
So a *low* sensorid (7) is quite uncommon for a *late* evtime? This
would help understand the problem. Unfortunately I have no clue what
to do about it. :-(
>Having spent a fair amount of time looking at the two following plans,
>it seems as though an additional statistic is needed to change the
>cost of doing an index lookup when the index is linearly ordered.
I'm not sure I understand what you mean by "index is linearly
ordered", but I guess correlation is that statistic you are talking
about. However, it is calculated per column, not per index.
>Whether CLUSTER does this or not, I don't know,
If you CLUSTER on an index and then ANALYSE, you get a correlation of
1.0 (== optimum) for the first column of the index.
> I never heard back
>from him after getting the runtime down to a few ms. :-/
Pity! I'd have liked to see EXPLAIN ANALYSE for
SELECT *
FROM mss_fwevent
WHERE sensorid = 7
AND evtime > (now() - '6 hours'::INTERVAL)
AND NOT action;
SELECT *
FROM mss_fwevent
WHERE sensorid = 7
AND evtime > (now() - '6 hours'::INTERVAL);
SELECT *
FROM mss_fwevent
WHERE evtime > (now() - '6 hours'::INTERVAL);
SELECT *
FROM mss_fwevent
WHERE sensorid = 7;
> Are indexes
>on linearly ordered data rebalanced somehow? I thought CLUSTER only
>reordered data on disk. -sc
AFAIK CLUSTER re-creates all indices belonging to the table.
Servus
Manfred
