Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 55F239FB864 for ; Fri, 29 Jun 2007 18:01:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 90494-01 for ; Fri, 29 Jun 2007 18:01:22 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mexforwardwc.lss.emc.com (unknown [137.69.117.200]) by postgresql.org (Postfix) with ESMTP id EBD179FB905 for ; Fri, 29 Jun 2007 18:01:31 -0300 (ADT) Received: from mailhubwc.lss.emc.com (nioo.lss.emc.com [137.69.224.86]) by mexforwardwc.lss.emc.com (Switch-3.2.5/Switch-3.1.7) with ESMTP id l5TL16KK026364; Fri, 29 Jun 2007 14:01:06 -0700 (PDT) Received: from avaexch01.avamar.com (avaexch01.avamar.com [128.222.199.14]) by mailhubwc.lss.emc.com (Switch-3.2.5/Switch-3.1.7) with ESMTP id l5TL13qk002869; Fri, 29 Jun 2007 14:01:04 -0700 (PDT) Received: from 128.222.235.22 ([128.222.235.22]) by avaexch01.avamar.com ([128.222.199.14]) with Microsoft Exchange Server HTTP-DAV ; Fri, 29 Jun 2007 21:01:03 +0000 Received: from nickel.avamar.com by avaexch01; 29 Jun 2007 14:01:03 -0700 Subject: Re: From: Ed Tyrrill To: Tom Lane Cc: pgsql-performance@postgresql.org In-Reply-To: <8930.1182820034@sss.pgh.pa.us> References: <7008.1182809415@sss.pgh.pa.us> <1182816598.6477.31.camel@nickel.avamar.com> <8930.1182820034@sss.pgh.pa.us> Content-Type: text/plain Content-Transfer-Encoding: 7bit Date: Fri, 29 Jun 2007 14:01:03 -0700 Message-Id: <1183150863.6477.49.camel@nickel.avamar.com> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 (2.0.2-27.rhel4.6) X-PMX-Version: 4.7.1.128075, Antispam-Engine: 2.5.1.298604, Antispam-Data: 2007.6.29.134433 X-PerlMx-Spam: Gauge=, SPAM=1%, Reasons='EMC_FROM_0+ -3, SUPERLONG_LINE 0.05, __CT 0, __CTE 0, __CT_TEXT_PLAIN 0, __HAS_MSGID 0, __HAS_X_MAILER 0, __MIME_TEXT_ONLY 0, __MIME_VERSION 0, __SANE_MSGID 0, __STOCK_PHRASE_25 0' X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/651 X-Sequence-Number: 25684 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