On Thu, 28 Feb 2002 at 09:51, Tom Lane wrote:
> Reinhard Max <[EMAIL PROTECTED]> writes:
> > I've just found a case where forcing indexscans results in much higher
> > speed.
>
> > -> Index Scan using foo_pkey on foo
> > (cost=0.00..25153.18 rows=352072 width=4)
> > (actual time=0.03..157.57 rows=38432 loops=1)
>
> The major estimation error is evidently in this indexscan. What
> statistics does pg_stats show for this table?
See attached file.
BTW, I've just done the same test on PostgreSQL 7.1 and got similar
results.
cu
Reinhard
tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals
|
most_common_freqs |
histogram_bounds
| correlation
-----------+---------+-----------+-----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
foo | id | 0 | 4 | -1 |
|
|
{10000028,10044844,10084132,10127060,10168908,10208677,10251756,10298604,10337189,10383913,10423409}
| -0.563276
foo | name | 0 | 21 | 32304 | {"Andreas M�ller","Dirk
B�cker","Frank M�ller","Horst Graf","Marc M�ller","Martin Schneider","SCS
GmbH","Stefan Schmidt","Thorsten Fischer"} |
{0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667}
| {"1 & 1 Internet Service GmbH","Berufsfachschule f�r Krankenpf' lege am KKH
Dinkelsb�hl","Dina Schreckeis","Gerhard Lahr","Institute for Enzymology","Lambrakis
Research Foundation' leanthis Kremmidas","Michael Brodersen","PlanConsult
Kraneis&Partner Gm' bH","Solutions 4 Ideas","Torsten Binias","�zcan Ferhat"} |
0.0231532
foo | street | 0 | 19 | 31973 | {" ","Berliner Str.
19","Otto-Hahn-Ring 6","Bahnhofstr. 54","Breslauer Str. 15","Dorfstr. 18","Dorfstr.
24","Gabelsberger Str. 12","Lindenstr. 20","M�hlenweg 2"} |
{0.007,0.001,0.001,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667}
| {"'Boskenna', Green Lane","Am Staubecken 1e","Brandenburger Weg
1","Fasanenstr. 7","Hauptstr. 52","Karl-Schmolz-Str 4","Margaretenstr. 8","Pfarrwiese
12a","Schneewittchenstr. 24","Univ.of Nottingham, Univ.Park","�ber der Elz 4"}
|
0.0199549
foo | zip | 0 | 8 | 19968 | {"
",99999,999,1210,61440,70771,80538,81739,10785,1090}
|
{0.018,0.00366667,0.00266667,0.00166667,0.00166667,0.00166667,0.00166667,0.00166667,0.00133333,0.00133333}
| {*,13127,25337,"3437 AE",44230,52078,63303,71397,81369,90403,"W6 8LJ"}
| 0.0547398
foo | city | 0 | 12 | 14577 |
{Berlin,M�nchen,Hamburg,Frankfurt,N�rnberg,Wien,Stuttgart,D�sseldorf,K�ln,Bonn}
|
{0.037,0.0266667,0.0216667,0.0116667,0.0103333,0.0103333,0.00833333,0.008,0.00766667,0.00733333}
| {*,Bochum,Dortmund,Gelnhausen,Herten,"Krumbach OT
Niederraunau",Mettingen,Paderborn,Schellerten,Valladolid,�tze}
| 0.0473121
foo | tel | 0 | 12 | 22744 | {" "}
| {0.3}
| {" 49
(0)441
798-2913",0039-0522971069,0170-9810773,0228-6420301,030-6931606,04084002600,05409-6465,06421-21327,07231/75220,089-31692761,972-3-6203038}
| 0.219408
foo | fax | 0 | 5 | 12 | {" "}
| {0.999667}
|
| 0.999994
(7 rows)
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
