PostgreSQL 7.1 forces sequence scan when there is no reason 
Author Message
 PostgreSQL 7.1 forces sequence scan when there is no reason

Hello,

I have a problem with PostgreSQL 7.1 forces sequence scan which is
almost 10 times slower than index scan.

Here is the information about query, tables, and data.

Any advise would be nice.

db=> \d listmembers
                             Table "listmembers"
  Column   |  Type   |                       Modifiers
-----------+---------+-------------------------------------------------------
- id        | integer | not null default
 nextval('"listmembers_id_seq"'::text) server_id | integer |
 name      | text    |
 email     | text    |
Indexes: listmembers_sid_key
Unique keys: listmembers_id_key,
             listmembers_sid_email_key
db=> \d listmembers_sid_key
Index "listmembers_sid_key"
  Column   |  Type
-----------+---------
 server_id | integer
btree
db=> \d listmembers_sid_email_key
Index "listmembers_sid_email_key"
  Column   |  Type
-----------+---------
 server_id | integer
 email     | text
unique btree
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
db=> select count(*) from listmembers where server_id = 15182;
 count
-------
 10011
(1 row)
db=> select count(*) from listmembers;
  count
---------
 1372425
(1 row)
db=> select server_id, count(*) from listmembers group by
 server_id order by count(*) desc limit 30; server_id | count
-----------+-------
     34062 | 43154
     32715 | 32232
     42495 | 31034
     38013 | 15905
     13746 | 15237
     42434 | 12045
     31696 | 10539
     15413 | 10334
     15182 | 10011
     28178 | 10000
     33330 | 10000
     37071 |  9960
     46532 |  9860
     15392 |  9737
     29643 |  9423
     36503 |  9329
     25378 |  9280
     32095 |  9079
     28621 |  9047
     15400 |  9043
     25870 |  8972
     38377 |  8724
     13046 |  8484
     42416 |  8442
     14869 |  7973
     24131 |  7940
     32625 |  7918
     46480 |  7783
     43172 |  7179
     36849 |  6887
(30 rows)
webmailstation=# select * from pg_class where relname='listmembers';
   relname   | reltype | relowner | relam | relfilenode | relpages |
 reltuples  | reltoastrelid | reltoastidxid | relhasindex | relisshared |
 relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs
 | relhasoids | relhaspkey | relhasrules | relhassubclass | relacl
 -------------+---------+----------+-------+-------------+----------+--------
-----+---------------+---------------+-------------+-------------+---------+-
---------+-----------+-------------+----------+----------+---------+---------
---+------------+-------------+----------------+-------- listmembers |
 6429403 |      102 |     0 |     6429402 |    14224 | 1.37241e+06 |      
 6429404 |             0 | t           | f           | r       |        4 |  
       0 |           0 |        0 |        0 |       0 | t          | f      
    | f           | f              | (1 row)
db=# select * from pg_statistic where starelid=6429402 ;
 starelid | staattnum | stanullfrac | stawidth | stadistinct | stakind1 |
 stakind2 | stakind3 | stakind4 | staop1 | staop2 | staop3 | staop4 |        
                                         stanumbers1                        
                         | stanumbers2 | stanumbers3  | stanumbers4 |        

                              stavalues1                                    

 |                                                                          
                                 stavalues2                                  
                                                                          |
 stavalues3 | stavalues4
 ----------+-----------+-------------+----------+-------------+----------+---
-------+----------+----------+--------+--------+--------+--------+-----------
-----------------------------------------------------------------------------
----------------------+-------------+--------------+-------------+-----------
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
---------------------------------------------------------------------------+-
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
-----------------------------------------------------------------------+-----
-------+------------ 6429402 |         1 |           0 |        4 |        
 -1 |        2 |        3 |        0 |        0 |     97 |     97 |      0 |
     0 |                                                                    
                                         | {0.805365}  |              |      
       |
 {590,520800,790589,1001533,1375234,1655946,1926816,2342644,2808910,3347435,3
532408}                                                                      

         |                                                                  

     |            | 6429402 |         2 |           0 |        4 |      
 1150 |        1 |        2 |        3 |        0 |     96 |     97 |     97
 |      0 |
 {0.038,0.027,0.024,0.0143333,0.012,0.01,0.00933333,0.00766667,0.00733333,0.0
07}                              |             | {0.428932}   |             |
 {34062,32715,42495,15182,38013,36503,13746,46532,42434,15392}              

         |
 {12855,15419,16223,20598,26019,30733,34402,38139,40811,44986,49509}        

                                                                        |    
        | 6429402 |         3 |   0.0376667 |       10 |        2581 |      
 1 |        2 |        3 |        0 |     98 |    664 |    664 |      0 |
 {0.466333,0.00333333,0.003,0.002,0.002,0.00166667,0.00133333,0.00133333,0.00
1,0.001}                         |             | {0.227739}   |             |
 {"",webwizards,"The Ad Builder Safe List","Kane at InternetSeer","Network
 Commerce",Michael,James,John,Admin,"D.Woodward "}                          

            | {" Success Center","Brent Sommers",Debra,"Great
 Vacations","Johnny Blaze",Mariani,"Peter Maglione","Silhouettes
 Catalog",Wally,"johan kotze",???}                                          
                                     |            | 6429402 |         4 |    
       0 |       25 |   -0.118184 |        1 |        2 |        3 |        0
 |     98 |    664 |    664 |      0 |
 {0.00133333,0.001,0.001,0.000666667,0.000666667,0.000666667,0.000666667,0.00
0666667,0.000666667,0.000666667} |             | {-0.0167706} |             |



alf.com} |



        | (4 rows)

-------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.***.com/



Fri, 05 Nov 2004 21:28:59 GMT
 PostgreSQL 7.1 forces sequence scan when there is no reason

Quote:

> 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.

Quote:
> 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?

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Fri, 05 Nov 2004 22:51:47 GMT
 PostgreSQL 7.1 forces sequence scan when there is no reason

Quote:

> > 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.

Quote:
> > 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 |

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

|  -0.0167706
(4 rows)

--
Denis

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly



Fri, 05 Nov 2004 23:40:06 GMT
 PostgreSQL 7.1 forces sequence scan when there is no reason

Quote:


>> 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.

Look at the ctid column for those records.  The range of block numbers
in the ctids would tell the tale.  I don't think Postgres itself
provides any operations on type TID, but you could dump the info into
a file and then analyze it.

Quote:
>  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

Hmm.  Correlation 0.43 is high enough to suggest that there's some
clustering effect.  If you look in the archives there's been prior
discussion about whether to make the optimizer weight the correlation
factor more strongly.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly



Sat, 06 Nov 2004 00:27:11 GMT
 PostgreSQL 7.1 forces sequence scan when there is no reason

Quote:
> >  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

> Hmm.  Correlation 0.43 is high enough to suggest that there's some
> clustering effect.  If you look in the archives there's been prior
> discussion about whether to make the optimizer weight the correlation
> factor more strongly.

I have increased sequence scan weight 5 times. And it uses index scan now, but
this is not the solution. I will have a look in the archives.

--
Denis

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Sat, 06 Nov 2004 22:40:09 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. PostgreSQL 7.1 and Sequences

2. when will PostgreSQL 7.1?

3. LargeObject in postgresql 7.1

4. debian packages for Postgresql 7.1 or higher ?

5. Success: PostgreSQL 7.1 on MacOS X (Darwin 1.3)

6. Postgresql 7.1 Beta 5, Postmaster dead

7. PostGreSql 7.1

8. debian packages for Postgresql 7.1 or higher ?

9. postgresql 7.1 file descriptor

10. Postgresql.exe 7.1 for M$ OS

11. Exporting data from PostgreSQL 7.1

12. problem postgresql 7.1 with command DELETE.


 
Powered by phpBB® Forum Software