Re: benchmarking the query planner (was Re: Simple postgresql.conf wizard)

That might only be the case when the pg_statistic record is in shared  
buffers.

Also I wonder if eqjoinsel and company might need to be made more  
toast-aware by detoasring all the things it needs once rather than  
every time it accesses them.

greg

On 6 Dec 2008, at 06:19 PM, "Robert Haas" <robertmhaas@gmail.com> wrote:

> Sorry for top posting but we are getting a bit far afield from the
> original topic.  I followed up the tests I did last night:
>
> http://archives.postgresql.org/pgsql-hackers/2008-12/msg00369.php
>
> I benchmarked 100 iterations of EXPLAIN on the query Greg Stark put
> together as a synthetic benchmark for default_statistics_target with
> various values for "SET STATISTICS n".  Testing was done on CVS HEAD
> on my laptop with no configure options other than --prefix.  Then I
> did this, to disable compression on pg_statistic.
>
> alter table pg_statistic alter column stanumbers1 set storage  
> external;
> alter table pg_statistic alter column stanumbers2 set storage  
> external;
> alter table pg_statistic alter column stanumbers3 set storage  
> external;
> alter table pg_statistic alter column stanumbers4 set storage  
> external;
> alter table pg_statistic alter column stavalues1 set storage external;
> alter table pg_statistic alter column stavalues2 set storage external;
> alter table pg_statistic alter column stavalues3 set storage external;
> alter table pg_statistic alter column stavalues4 set storage external;
>
> (Note that you'll need to put allow_system_table_mods=true in your
> postgresql.conf file if you want this to work.)  Then I reran the
> tests.  The results were pretty dramatic.  In the table below, the
> first column is value of "SET STATISTICS n" that was performed the
> table column prior to analyzing it.  The second column is the time
> required to plan the query 100x AFTER disabling compression on
> pg_statistic, and the third column is the time required to plan the
> query 100x BEFORE disabling compression on pg_statistic.
>
> 10    0.829202    0.8249
> 20    1.059976    1.06957
> 30    1.168727    1.143803
> 40    1.287189    1.263252
> 50    1.370167    1.363951
> 60    1.486589    1.460464
> 70    1.603899    1.571107
> 80    1.69402    1.689651
> 90    1.79068    1.804454
> 100    1.930877    2.803941
> 150    2.446471    4.833002
> 200    2.95323    6.217708
> 250    3.436741    7.507919
> 300    3.983568    8.895015
> 350    4.497475    10.201713
> 400    5.072471    11.576961
> 450    5.615272    12.933128
> 500    6.286358    14.408157
> 550    6.895951    15.745378
> 600    7.400134    17.192916
> 650    8.038159    18.568616
> 700    8.606704    20.025952
> 750    9.154889    21.45775
> 800    9.80953    22.74635
> 850    10.363471    24.057379
> 900    11.022348    25.559911
> 950    11.69732    27.021034
> 1000    12.266699    28.711027
>
> As you can see, for default_statistics_target > 90, this is a HUGE  
> win.
>
> After doing this test, I rebuilt with --enable-profiling and profiled
> EXPLAIN 10x with SET STATISTICS 10, 70, 100, 200 with a vanilla
> configuration, and then 200 again with compression turned off as
> described above.  The, ahem, ridiculously small limit on attachment
> size prevents me from attaching the full results, so please see the
> attached results which are truncated after the first section.  10x
> doesn't seem to be quite enough to get the exact picture of where the
> bottlenecks are, but the overall picture is clear enough:
> decompression introduces a huge overhead.
>
> Looking specifically at the 200-decompress output, the next biggest
> hit is AllocSetAlloc(), which, from the detailed results that I
> unfortunately can't include, is being called mostly by datumCopy()
> which is being called mostly by get_attstatsslot().  There are 4000
> calls to get_attstatsslot() which result 701,500 calls to datumCopy().
>
> I'm not too sure what any of this means in terms of optimizatiion,
> other than that changing the storage type of pg_statistic columns to
> external looks like a huge win.  Perhaps someone more knowledgeable
> than I has some thoughts.
>
> ...Robert
> <gmon-summary.tbz>