Re: PostgreSQL 7.1 forces sequence scan when there is no reason

On Monday 20 May 2002 21:48, Tom Lane wrote:
> Denis Perchine <dyp(at)perchine(dot)com> writes:
> > db=> explain analyze select count(*) from listmembers where
> > server_id = 15182; NOTICE: QUERY PLAN:
> > Aggregate (cost=31428.34..31428.34 rows=1 width=0) (actual
> > time=38632.84..38632.84 rows=1 loops=1) -> Seq Scan on listmembers
> > (cost=0.00..31379.16 rows=19671 width=0) (actual time=198.13..38494.36
> > rows=10011 loops=1) Total runtime: 38633.01 msec
> > EXPLAIN
> > db=> set enable_seqscan to no;
> > SET VARIABLE
> > db=> explain analyze select count(*) from listmembers where
> > server_id = 15182; NOTICE: QUERY PLAN:
> > Aggregate (cost=60210.41..60210.41 rows=1 width=0) (actual
> > time=2117.61..2117.61 rows=1 loops=1) -> Index Scan using
> > listmembers_sid_key on listmembers (cost=0.00..60161.24 rows=19671
> > width=0) (actual time=81.41..2096.67 rows=10011 loops=1) Total runtime:
> > 2117.83 msec EXPLAIN
>
> Hm. Is it possible that the rows with server_id = 15182 are clustered
> together? Given that you are fetching 10011 rows from a 14224-page
> table, it seems unlikely that an indexscan could be such a big win
> unless there was a very strong clustering effect.

Possible, but 10 000 records are less than 1% of all records.
How can I figure out whether they are clustered.

> > db=# select * from pg_statistic where starelid=6429402 ;
>
> This is pretty unhelpful (not to mention unreadable) since we have no
> idea which row is which. Could we see the pg_stats view, instead?

db=# select * from pg_stats where tablename='listmembers';
tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals
| most_common_freqs
|
histogram_bounds
| correlation
-------------+-----------+-----------+-----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
listmembers | id | 0 | 4 | -1 |
|
|
{590,520800,790589,1001533,1375234,1655946,1926816,2342644,2808910,3347435,3532408}
| 0.805365
listmembers | server_id | 0 | 4 | 1150 |
{34062,32715,42495,15182,38013,36503,13746,46532,42434,15392}
|
{0.038,0.027,0.024,0.0143333,0.012,0.01,0.00933333,0.00766667,0.00733333,0.007}
| {12855,15419,16223,20598,26019,30733,34402,38139,40811,44986,49509}
| 0.428932
listmembers | name | 0.0376667 | 10 | 2581 |
{"",webwizards,"The Ad Builder Safe List","Kane at InternetSeer","Network
Commerce",Michael,James,John,Admin,"D.Woodward "}
|
{0.466333,0.00333333,0.003,0.002,0.002,0.00166667,0.00133333,0.00133333,0.001,0.001}
| {" Success Center","Brent Sommers",Debra,"Great Vacations","Johnny
Blaze",Mariani,"Peter Maglione","Silhouettes Catalog",Wally,"johan
kotze",жЛжН}
| 0.227739
listmembers | email | 0 | 25 | -0.118184 |
{servicebox(at)spedia(dot)net,dougsreplyto3(at)excite(dot)com,inquire(at)careerexpansion(dot)com,234freeb(at)webwizards-add-url(dot)com,cashdueu(at)hotmail(dot)com,cashonline1(at)excite(dot)com,cwmailer(at)yahoo(dot)com,galaxy(at)mail2(dot)galaxy(dot)com,gmichel(at)post(dot)com,half(dot)com_by_ebay(at)half(dot)com}
|
{0.00133333,0.001,0.001,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667}
|
{05078475(at)email(dot)com,bethebest(at)zwallet(dot)com,cynric7(at)yahoo(dot)com,ezine(at)yourhomejob(dot)com,ilkst(at)beeline(dot)samara(dot)ru,kirk(dot)stensrud(at)lpl(dot)com,mjm(at)netset(dot)com,ping13013(at)yahoo(dot)fr,sandrac(at)menta(dot)net,tgaeke(at)worldsubmitter(dot)com,zzzmuffin(at)aol(dot)com}
| -0.0167706
(4 rows)

--
Denis