Thread: [Fwd: Re: Very poor estimates from planner]

[Fwd: Re: Very poor estimates from planner]

From
Rod Taylor
Date:
On Thu, 2003-11-06 at 10:35, Tom Lane wrote:
> Rod Taylor <rbt@rbt.ca> writes:
> >> ->  Hash Join  (cost=3D1230.79..60581.82 rows=3D158 width=3D54)=
> >  (actual time=3D1262.35..151200.29 rows=3D1121988 loops=3D1)
> >> Hash Cond: ("outer".account_id =3D "inner".account_id)
> >> ->  Hash Join  (cost=3D1226.78..52863.43 rows=3D1542558 w=
> > idth=3D50) (actual time=3D1261.63..100418.30 rows=3D1573190 loops=3D1)
> >> (join of bsod, tsb, tss)
> 
> (btw, would you mind turning off MIME encoding in your mails to the PG
> lists?  It's a real PITA to quote.)

I can, though I would ask which email client you use that doesn't pull
content out of mime encoded emails.

> > So yes, since this is a full table scan all values will be joined since
> > the foreign key enforces them all to exist.
> 
> Well, no, because only 1121988 rows come out of the join when 1573190
> went in.  So the actual selectivity of the join is about 70%.  The
> question is why the planner is estimating the selectivity at 0.01%
> (158/1542558).
> 
> Could we see the pg_stats rows for service.account_id and
> account.account_id?
relname |  attname   | stanullfrac | stawidth | stadistinct | stakind1
| stakind2 | stakind3 | stakind4 | staop1 | staop2 | staop3 | staop4
|                                          
stanumbers1                                            | stanumbers2 |
stanumbers3 | stanumbers4 |                          
stavalues1                            | stavalues2 | stavalues3 |
stavalues4

---------+------------+-------------+----------+-------------+----------+----------+----------+----------+--------+--------+--------+--------+--------------------------------------------------------------------------------------------------+-------------+-------------+-------------+-----------------------------------------------------------------+------------+------------+------------service
|account_id |           0 |        4 |          10 |        1
 
|        3 |        0 |        0 |     96 |     97 |      0 |      0 |
{0.388393,0.0825893,0.078125,0.0758929,0.0703125,0.0647321,0.0647321,0.0636161,0.0625,0.0491071} | {0.591672}  |
    |             | {1,8221,8223,8226,8222,8218,8220,8219,8224,8225}                |            |   |account |
account_id|           0 |        4 |          -1 |        2
 
|        3 |        0 |        0 |     97 |     97 |      0 |      0
|                                                                                                  | {0.97034}   |
      |             | {1,10178,12136,14099,16054,18011,19966,21924,23881,26018,27995} |            |   |
 
(2 rows)

-- 
Rod Taylor <rbt [at] rbt [dot] ca>

Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/rbtpub.asc



Re: [Fwd: Re: Very poor estimates from planner]

From
Rod Taylor
Date:
> > Could we see the pg_stats rows for service.account_id and
> > account.account_id?

Sorry, ignore previous numbers. My prior tests were done in a
transaction (to roll back stats changes) and I forgot to re-analyze.
relname |  attname   | stanullfrac | stawidth | stadistinct | stakind1
| stakind2 | stakind3 | stakind4 | staop1 | staop2 | staop3 | staop4
|
stanumbers1                                              | stanumbers2 |
stanumbers3 | stanumbers4
|






















                                                                                                             stavalues1























                                                                                             | stavalues2 | stavalues3
|stavalues4 

service | account_id |           0 |        4 |          10 |        1
|        3 |        0 |        0 |     96 |     97 |      0 |      0 |
{0.392491,0.0819113,0.0773606,0.0750853,0.0716724,0.0648464,0.0637088,0.0637088,0.0614334,0.0477816} | {0.59306}   |
        |             | {1,8221,8223,8226,8222,8219,8218,8220,8224,8225}
















































                     |            |            |account | account_id |           0 |        4 |          -1 |        2 
|        3 |        0 |        0 |     97 |     97 |      0 |      0
|                                                                                                      | {0.970345}  |
          |             |
{1,8236,8256,8275,8295,8314,8334,8353,8373,8392,8412,8431,8451,8471,8491,8511,8530,8551,8570,8590,8609,8629,8649,8669,8688,8708,8728,8747,8767,8786,8806,8825,8845,8864,8884,8903,8923,8942,8962,8982,9001,9021,9041,9061,9080,9100,9119,9139,9158,9178,9197,9217,9238,9257,9277,9296,9316,9335,9355,9374,9394,9413,9433,9453,9473,9493,9512,9532,9551,9571,9590,9610,9629,9649,9668,9688,9707,9727,9747,9766,9786,9805,9825,9844,9864,9883,9903,9922,9942,9961,9982,10002,10021,10041,10060,10080,10099,10119,10138,10158,10178,10198,10217,10238,10258,10277,10297,10316,10336,10355,10375,10394,10414,10433,10453,10472,10492,10512,10531,10551,10570,10590,10609,10629,10648,10668,10687,10707,10726,10746,10766,10785,10805,10824,10844,10863,10883,10902,10922,10941,10961,10980,11000,11020,11039,11059,11078,11098,11117,11137,11156,11176,11195,11216,11236,11255,11275,11294,11314,11333,11353,11373,11393,11412,11432,11451,11471,11491,11510,11530,11549,11569,11588,11608,11627,11647,11666,11686,11705,11725,11745,11764,11784,11803,11823,11842,11862,11881,11901,11920,11940,11959,11979,11999,12018,12039,12058,12078,12097,12117,12136,12156,12176,12196,12215,12235,12255,12274,12294,12313,12333,12352,12372,12391,12411,12430,12450,12469,12489,12509,12528,12548,12567,12587,12607,12627,12646,12666,12685,12705,12724,12744,12764,12783,12804,12823,12843,12862,12882,12901,12921,12940,12960,12979,12999,13021,13040,13060,13079,13099,13119,13140,13159,13179,13198,13218,13237,13257,13277,13296,13316,13335,13355,13374,13394,13413,13433,13452,13472,13491,13511,13531,13550,13570,13589,13609,13628,13648,13667,13687,13706,13726,13745,13765,13786,13805,13825,13844,13864,13883,13903,13922,13942,13961,13982,14002,14021,14041,14060,14080,14099,14119,14138,14158,14178,14198,14217,14237,14257,14276,14296,14315,14335,14354,14374,14393,14413,14432,14452,14471,14491,14511,14530,14550,14569,14589,14608,14628,14647,14667,14686,14706,14725,14745,14765,14784,14804,14823,14843,14862,14882,14901,14921,14940,14960,14979,14999,15019,15038,15058,15077,15097,15116,15136,15155,15175,15194,15214,15233,15253,15273,15292,15312,15331,15351,15370,15390,15409,15429,15448,15468,15487,15507,15527,15546,15566,15585,15605,15624,15644,15663,15683,15702,15722,15741,15761,15781,15800,15820,15839,15859,15878,15898,15917,15937,15956,15976,15995,16015,16035,16054,16074,16093,16113,16132,16152,16171,16191,16210,16230,16249,16269,16289,16308,16328,16347,16367,16386,16406,16426,16446,16465,16485,16504,16524,16544,16563,16583,16602,16622,16641,16661,16680,16700,16719,16739,16759,16778,16798,16817,16837,16856,16876,16895,16915,16934,16954,16973,16993,17013,17032,17052,17071,17091,17111,17131,17150,17171,17190,17210,17229,17249,17269,17288,17308,17327,17347,17366,17386,17405,17425,17444,17464,17483,17503,17523,17542,17562,17581,17601,17620,17640,17659,17679,17698,17718,17737,17757,17777,17796,17816,17835,17855,17874,17894,17913,17933,17952,17972,17991,18011,18031,18050,18070,18089,18109,18128,18148,18167,18187,18206,18226,18245,18265,18285,18304,18324,18343,18363,18382,18402,18421,18441,18460,18480,18499,18519,18539,18558,18578,18597,18617,18636,18656,18675,18695,18714,18734,18753,18773,18793,18812,18832,18851,18871,18890,18910,18929,18949,18968,18988,19007,19027,19047,19066,19086,19105,19125,19144,19164,19183,19203,19222,19242,19261,19281,19301,19320,19340,19359,19379,19398,19418,19437,19457,19476,19496,19516,19535,19556,19575,19595,19614,19634,19653,19673,19692,19712,19731,19751,19771,19790,19810,19829,19849,19868,19889,19908,19928,19947,19967,19986,20006,20026,20045,20065,20084,20104,20123,20143,20162,20182,20201,20221,20240,20260,20280,20299,20319,20338,20358,20377,20397,20416,20436,20455,20475,20494,20514,20534,20553,20573,20592,20612,20631,20651,20670,20690,20709,20730,20749,20769,20789,20808,20828,20847,20867,20886,20906,20925,20945,20964,20984,21003,21023,21043,21062,21082,21101,21121,21140,21160,21179,21199,21218,21238,21257,21277,21297,21316,21336,21355,21375,21395,21415,21434,21454,21473,21493,21512,21532,21552,21571,21591,21610,21630,21649,21669,21688,21708,21727,21747,21766,21786,21808,21827,21847,21866,21886,21905,21925,21944,21964,21983,22003,22022,22042,22062,22081,22101,22120,22140,22159,22179,22198,22218,22237,22257,22277,22296,22316,22335,22356,22375,22395,22414,22434,22453,22473,22492,22512,22532,22551,22571,22590,22610,22629,22649,22668,22688,22707,22727,22746,22766,22786,22805,22825,22844,22864,22883,22903,22922,22942,22961,22981,23000,23020,23040,23059,23079,23098,23118,23137,23157,23176,23196,23215,23235,23254,23274,23294,23313,23333,23352,23372,23391,23411,23430,23450,23469,23489,23509,23530,23550,23569,23589,23608,23628,23647,23667,23686,23706,23725,23745,23764,23784,23804,23823,23843,23862,23882,23901,23921,23940,23960,23979,23999,24018,24038,24058,24077,24097,24116,24136,24155,24175,24194,24214,24233,24253,24272,24292,24312,24331,24351,24370,24390,24409,24429,24448,24468,24488,24508,24527,24547,24567,24586,24606,24626,24646,24665,24685,24704,24724,24744,24764,24783,24803,24823,24842,24862,24891,24921,24945,24974,24996,25021,25042,25062,25082,25101,25122,25141,25167,25227,25247,25266,25286,25307,25336,25377,25399,25419,25461,25484,25507,25530,25549,25569,25589,25611,25633,25653,25672,25692,25712,25733,25755,25774,25794,25813,25833,25852,25872,25891,25911,25930,25956,25980,25999,26019,26038,26058,26077,26097,26116,26136,26155,26176,26195,26215,26235,26254,26274,26293,26313,26332,26352,26374,26394,26413,26433,26452,26472,26492,26511,26531,26550,26570,26589,26609,26628,26648,26667,26687,26706,26726,26746,26765,26785,26804,26824,26843,26863,26882,26905,26924,26944,26963,26985,27005,27024,27044,27067,27087,27106,27126,27145,27166,27185,27205,27226,27246,27266,27285,27305,27324,27344,27366,27386,27405,27427,27446,27466,27485,27505,27525,27544,27564,27583,27603,27622,27642,27661,27681,27700,27720,27739,27759,27779,27798,27818,27837,27857,27876,27896,27915,27935,27954,27976,28015}
|           |            | 
(2 rows)
dev_iqdb=# select distinct(account_id) from service.service;account_id
------------         1      8218      8219      8220      8221      8222      8223      8224      8225      8226
(10 rows)