Re: Query performance discontinuity

On Fri, 15 Nov 2002 03:26:32 +0000 (UTC), in
comp.databases.postgresql.performance you wrote:

>        ->  Seq Scan on ps2  (cost=0.00..13783.40 rows=327895 width=179)
                                          ^^^^^
>(actual time=0.15..15211.49 rows=327960 loops=1)
>
>  ->  Index Scan using ps2_idx on ps2  (cost=0.00..881616.45 rows=327895
                                                    ^^^^^^
>width=179) (actual time=40.38..2151.38 rows=59629 loops=1)
                                ^^^^
>
>The ps2 table is in time_stamp order, but the tstarts aren't quite as
>good -- they're mostly there, but they're computed by subtracting a
>(stochastic) value from time_stamp.

Mike,

this is the well known "divide correlation by number of index columns"
effect.  This effect can be masked to a certain degree by reducing
random_page_cost, as has already been suggested.

The estimated index scan cost is also influenced by
effective_cache_size; its default value is 1000.  Try

    SET effective_cache_size = 50000;

This should help a bit, but please don't expect a big effect.

I'm running Postgres 7.2 with a modified index cost estimator here.
The patch is at http://www.pivot.at/pg/16-correlation.diff

This patch gives you two new GUC variables.

index_cost_algorithm:  allows you to select between different methods
of interpolating between best case and worst case.  0 is the standard
behavior (before the patch), 1 to 4 tend more and more towards lower
index scan costs.  See the switch statement in costsize.c for details.
Default = 3.

secondary_correlation:  is a factor that is used to reduce the
correlation of the first index column a little bit once for each
additional index column.  Default = 0.95.

With default settings you should get an index cost estimate between
20000 and 30000.  Which allows you to increase random_page_cost to a
more reasonable value of something like 10 or even higher.

If you try it, please let me know how it works for you.

Servus
 Manfred