Re: Moving postgresql.conf tunables into 2003...

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