Re: - Mailing list pgsql-performance

On Mon, 2007-06-25 at 21:07 -0400, Tom Lane wrote:
> It's worth fooling around with work_mem just to see what happens.  The
> other thing that would be interesting is to force the other plan (set
> enable_mergejoin = off) just to see what the planner is costing it at.
> My suspicion is that the estimated costs are pretty close.
>
> The ANALYZE stats affect this choice only in second-order ways AFAIR.
> The planner penalizes hashes if it thinks there will be a lot of
> duplicate values in the inner relation, but IIRC there is also a penalty
> for inner duplicates in the mergejoin cost estimate.  So I'm a bit
> surprised that there'd be a change.
>
> Can you show us the pg_stats rows for the join columns after analyzing
> at target 10 and target 100?
>
>             regards, tom lane

I wasn't able to work on this for a couple days, but now I am back on it
again.  I increased work_mem to 1GB, and decreased
default_statistics_target to 10.  postmaster takes 74.8% of RAM (out of
4GB) with shared_memory = 1GB as well.  I have not been able to get the
database to use the plan that was really fast the first time.  So
perhaps the random sample factor is what caused it to choose the faster
plan the first time.

Tom, as you requested here are the pg_stats rows with
default_statistics_target = 10:

mdsdb=# select * from pg_stats where attname = 'record_id';
 schemaname |    tablename    |  attname  | null_frac | avg_width |
n_distinct  | most_common_vals | most_common_freqs |
histogram_bounds                                       | correlation
------------+-----------------+-----------+-----------+-----------
+-------------+------------------+-------------------
+----------------------------------------------------------------------------------------------+-------------
 public     | backup_location | record_id |         0 |         8 |
4.40637e+06 | {6053595}        | {0.000666667}     |
{25859,1940711,2973201,4592467,5975199,8836423,10021178,10261007,11058355,12087662,14349748} |    0.165715
 public     | backupobjects   | record_id |         0 |         8 |
-1 |                  |                   |
{10565,1440580,2736075,4140418,5600863,7412501,8824407,10136590,11560512,13069900,14456128}  |    0.902336

and default_statistics_target = 100:

mdsdb=# select * from pg_stats where attname = 'record_id';
 schemaname |    tablename    |  attname  | null_frac | avg_width |
n_distinct  |
most_common_vals                                      |
most_common_freqs                                  |
histogram_bounds
| correlation
------------+-----------------+-----------+-----------+-----------
+-------------

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------!

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------!
 --------------------------------------------------------------!
 --------

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
 public     | backup_location | record_id |         0 |         8 |
5.82337e+06 |

{235096,295262,1553025,1612535,1635617,1803461,2000641,2036309,2507381,2904177,2921981,3089088,3146908,3224744,3253356,3580055,3647668,4660094,4661032,4752775,4801371,5116051,5173423,9891458,9895966,9897668,9905497,9907478,9908664,9913842,9916856,9929495,9946579,9957084,9962904,9963807,9971068,9980253,9985117,9985892,10007476,10010352,10010808,10025192,10075013,10103597,10115103,10116781,10120165,10137641,10141427,10144210,10148637,10369082,10395553,10418593,10435057,10441855,10497439,10499683,10509766,10515351,10521300,10522302,10525281,10538714,10542612,10544981,10546440,10678033,10995462,11101727,11132055,12664343,12967575}
|
{6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.6!

6667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05}
|
{11545,295289,430988,565826,912519,1179853,1442886,1590646,1649901,1709198,1773893,1831398,1966887,2026312,2087874,2151518,2316639,2474245,2571004,2769480,2863837,2952117,3100933,3182580,3259831,3338022,3412802,3517509,3671705,3758894,4106521,4549800,4620521,4699748,4772724,4851063,4927467,5028209,5105421,5183582,5364296,5454952,5965286,6081539,6528031,6798065,7192136,7518897,7854942,8169821,8527085,8867514,9318637,9812968,9896732,9!
 915321,9933027,9950345,9969581,9987324,10004114,10022269,10040!
 935,1005

9618,10077611,10096111,10114682,10132165,10151207,10168791,10232857,10299111,10370156,10441842,10497303,10514993,10531984,10678040,10953841,11030018,11088408,11153327,11214573,11443648,11507997,11566711,11615011,11683984,11909042,12014715,12106151,12194283,12284176,12373145,12456035,12545752,12628686,12723672,13022336,13621556,14449465}
|   0.210513 
 public     | backupobjects   | record_id |         0 |         8 |
-1 |                                       |
|

{621,167329,364075,495055,629237,768429,906683,1036819,1168225,1304782,1446441,1635583,1776623,1919568,2058804,2213573,2384816,2516367,2654165,2777015,2913726,3045319,3179436,3326044,3449751,3584737,3705100,3849567,3983587,4119532,4255086,4400700,4522294,4676257,4803235,4930094,5065599,5212568,5341881,5476010,5610455,5750156,5876952,6009086,6341074,6663749,6792397,6913638,7035450,7166345,7309759,7449436,7579067,7717768,7852692,7992611,8107334,8232850,8376448,8510463,8654839,8785467,8930354,9065437,9219398,9347145,9497479,9694222,9829935,9962878,10107465,10246453,10406586,10548493,10690983,10827832,10978600,11111459,11257696,11462706,11593369,11738262,11918473,12065317,12208496,12340088,12483168,12631769,12754208,12907042,13037605,13176218,13312853,13440791,13600318,13749132,13884632,14018915,14174415,14328234,14458641}
             |    0.911416