From: | Denis Perchine <dyp(at)perchine(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | PostgreSQL 7.1 forces sequence scan when there is no reason |
Date: | 2002-05-20 13:23:40 |
Message-ID: | 200205202023.40282.dyp@perchine.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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} | |
{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,c
wmailer(at)yahoo(dot)com,galaxy(at)mail2(dot)galaxy(dot)com,gmichel(at)post(dot)com,half(dot)com_by_ebay(at)h
alf.com} |
{05078475(at)email(dot)com,bethebest(at)zwallet(dot)com,cynric7(at)yahoo(dot)com,ezine(at)yourhomejo
b.com,ilkst(at)beeline(dot)samara(dot)ru,kirk(dot)stensrud(at)lpl(dot)com,mjm(at)netset(dot)com,ping13013@
yahoo.fr,sandrac(at)menta(dot)net,tgaeke(at)worldsubmitter(dot)com,zzzmuffin(at)aol(dot)com} |
| (4 rows)
-------------------------------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Wm. G. Urquhart | 2002-05-20 13:47:44 | Re: Further thoughts on Referential Integrity |
Previous Message | Joel Burton | 2002-05-20 13:19:48 | Re: Further thoughts on Referential Integrity |