On Wed, 2007-09-26 at 11:22 -0400, Tom Lane wrote:
> ... how
> many values of "a" are there really, and what's the true distribution of
> counts?
table_a has 23366 distinct values. Some statistics (using R):
> summary(table_a_histogram)
a count
Min. : 70000857 Min. : 1
1st Qu.:700003628 1st Qu.: 9
Median :700011044 Median : 22
Mean :622429573 Mean : 17640
3rd Qu.:700018020 3rd Qu.: 391
Max. :800003349 Max. :3347707
I'm not sure what you want to see in terms of distribution of counts, so
I created 2 plots: "a" against the counts for each distinct "a" value,
and the histogram of the log of the counts (without the log it's not
really readable). I hope they'll make it through to the list...
> Do the plan estimates get closer to reality if you set a higher
> statistics target?
The results of setting higher statistics targets are attached too. I
can't tell if the stats are closer to reality or not, but the plan
changes in any case...
Cheers,
Csaba.
Attachment:
table_a_counts.png
Description: PNG image
Attachment:
table_a_counts_histogram.png
Description: PNG image
db=# alter table temp_table_a ALTER a set statistics 100;
db=# analyze verbose temp_table_a;
INFO: analyzing "public.temp_table_a"
INFO: "temp_table_a": scanned 30000 of 655299 pages, containing 18870000 live rows and 0 dead rows; 30000 rows in sample, 412183071 estimated total rows
db=# select * from pg_stats where tablename = 'temp_table_a';
schemaname | tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation
------------+--------------+---------+-----------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
public | temp_table_a | a | 0 | 8 | 3320 | {700009411,700002317,700004630,700002843,700006505,700023682,700022128,700003583,700017957,700006025,700015988,700023482,700004117,700007205,700012136,700010476,700014833,700017217,700008694,700014251,700011345,700002683,700013155,800002849,700019049,700000762,700019048,700022028,700001252,700021793,700019097,700013291,700013588,700009119,700018429,700000158,700016990,700017956,700023992,70011667,700019493,700021411,700017871,700020154,700005466,700010475,700018637,700000897,700014785,700023244,800000335,80016184,700000761,700006691,700008961,700015131,700022290,700013232,700014831,700016187,700021053,700002541,700018414,700019738,700020192,700007416,700008945,700015991,700012285,700013309,700006364,700011228,700020174,700004361,700005736,700014249,700017890,700020669,70011584,700002454,700010069,700013082,700016098,80017430,80018012,700001286,700001795,700002325,700002721,700018164,700022384,80013130,700001517,800000032,700003137,700008230,700010374,700011299,700014197,700016434} | {0.00903333,0.00886667,0.00826667,0.008,0.00756667,0.00753333,0.00743333,0.00726667,0.00713333,0.00706667,0.007,0.00693333,0.00673333,0.00656667,0.00636667,0.0062,0.0062,0.00616667,0.00613333,0.006,0.00563333,0.0055,0.00463333,0.00423333,0.00396667,0.00373333,0.00343333,0.00343333,0.0031,0.00256667,0.0025,0.00236667,0.00233333,0.00213333,0.00213333,0.0021,0.00206667,0.00203333,0.00203333,0.002,0.002,0.00196667,0.00193333,0.00193333,0.00183333,0.00183333,0.00183333,0.0018,0.0018,0.0018,0.00176667,0.00173333,0.00173333,0.00173333,0.0017,0.0017,0.0017,0.00166667,0.00166667,0.00163333,0.0016,0.00156667,0.00156667,0.00156667,0.00156667,0.00153333,0.00153333,0.00153333,0.0015,0.0015,0.00146667,0.00146667,0.00146667,0.00143333,0.00143333,0.00143333,0.00143333,0.00143333,0.0014,0.0014,0.0014,0.0014,0.0014,0.00136667,0.00136667,0.00136667,0.00136667,0.00136667,0.00136667,0.00136667,0.00136667,0.00133333,0.00133333,0.00133333,0.0013,0.0013,0.0013,0.0013,0.0013,0.0013} | {70010184,80010631,80011534,80013683,80014332,80015011,80016113,80017127,80017990,80018206,80018579,80018815,700000082,700000556,700000949,700001464,700001797,700002009,700002342,700002536,700002895,700002996,700003288,700003570,700003934,700004172,700004478,700004748,700005072,700005370,700005493,700005791,700006004,700006263,700006598,700006822,700007062,700007409,700007572,700007792,700008233,700008565,700008795,700009047,700009276,700009539,700009717,700009877,700010029,700010227,700010503,700010931,700011122,700011406,700011652,700011851,700012122,700012287,700012598,700012856,700013144,700013308,700013475,700013790,700014103,700014357,700014443,700014756,700015046,700015377,700015671,700016037,700016345,700016745,700017024,700017221,700017575,700017876,700018283,700018676,700019090,700019409,700019877,700020116,700020513,700020803,700021114,700021388,700021797,700022133,700022374,700022577,700022921,700023447,700023681,700024112,700024460,800000171,800000572,800001802,800003180} | 0.862633
public | temp_table_a | b | 0 | 8 | 1.86656e+07 | {700773174,701001618,701208693,7004728327,7006203959,7006357115,7007974985,7009682696,7012068090,7013911212} | {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} | {41707835,700698980,803974484,7004815752,7007566186,7010477490,7013904387,7016651409,7020650387,7026239954,8001229552} | 0.657061
db=# prepare test_003(bigint) as
db-# SELECT tb.*
db-# FROM temp_table_a ta
db-# JOIN temp_table_b2 tb ON ta.b=tb.b
db-# WHERE ta.a = $1
db-# ORDER BY ta.a, ta.b
db-# limit 10;
db=# explain analyze execute test_003(31855344);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..22427.86 rows=10 width=24) (actual time=0.027..0.027 rows=0 loops=1)
-> Nested Loop (cost=0.00..625737.32 rows=279 width=24) (actual time=0.024..0.024 rows=0 loops=1)
-> Index Scan using temp_pk_table_a on temp_table_a ta (cost=0.00..133756.10 rows=124152 width=16) (actual time=0.023..0.023 rows=0 loops=1)
Index Cond: (a = $1)
-> Index Scan using temp_pk_table_b2 on temp_table_b2 tb (cost=0.00..3.95 rows=1 width=8) (never executed)
Index Cond: ("outer".b = tb.b)
Total runtime: 0.082 ms
---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
