From: Ed Tyrrill <tyrrill_ed@emc.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re:
Date: Fri, 29 Jun 2007 14:01:03 -0700
Message-ID: <1183150863.6477.49.camel@nickel.avamar.com> (raw)
In-Reply-To: <8930.1182820034@sss.pgh.pa.us>
References: <A23190A408F7094FAF446C1538222F76041E1C28@avaexch01.avamar.com>
<7008.1182809415@sss.pgh.pa.us>
<1182816598.6477.31.camel@nickel.avamar.com>
<8930.1182820034@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tyrrill_ed@emc.com, tgl@sss.pgh.pa.us
Subject: Re:
In-Reply-To: <1183150863.6477.49.camel@nickel.avamar.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox