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@perchine.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@spedia.net,dougsreplyto3@excite.com,inquire@careerexpansion.com,234freeb@webwizards-add-url.com,cashdueu@hotmail.com,cashonline1@excite.com,cwmailer@yahoo.com,galaxy@mail2.galaxy.com,gmichel@post.com,half.com_by_ebay@half.com}

|
{0.00133333,0.001,0.001,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667}
|

{05078475@email.com,bethebest@zwallet.com,cynric7@yahoo.com,ezine@yourhomejob.com,ilkst@beeline.samara.ru,kirk.stensrud@lpl.com,mjm@netset.com,ping13013@yahoo.fr,sandrac@menta.net,tgaeke@worldsubmitter.com,zzzmuffin@aol.com}

|  -0.0167706
(4 rows)

--
Denis