From: | Sergio Freue <sfreue(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Select not using primary key index |
Date: | 2002-03-14 03:07:54 |
Message-ID: | 3C90140A.4090508@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I'm trying to run a simple query on a big (1,500,000 rows) table and I'm
getting bad performance.
The table I'm using is the minimum necessary to show the problem:
create table testBig (id1 smallint not null,id2 smallint not
null,primary key (id1, id2));
Add about 1,500,000 records
Now a psql session:
testdb=# vacuum verbose analyze testbig;
NOTICE: --Relation testbig--
NOTICE: Pages 7792: Changed 0, reaped 0, Empty 0, New 0; Tup 1589472:
Vac 0, Keep/VTL 0/0, Crash 0
, UnUsed 0, MinLen 36, MaxLen 36; Re-using: Free/Avail. Space 0/0;
EndEmpty/Avail. Pages 0/0. CPU 1
.68s/0.40u sec.
NOTICE: Index testbig_pkey: Pages 5920; Tuples 1589472. CPU 1.35s/1.50u
sec.
NOTICE: Analyzing...
VACUUM
testdb=# select count(distinct id1) from testbig;
count
-------
2652
(1 row)
testdb=# select count(distinct id2) from testbig;
count
-------
2717
(1 row)
testdb=# explain select * from testBig where id1 = 1585 and id2 = 42;
NOTICE: QUERY PLAN:
Seq Scan on testbig (cost=0.00..31634.08 rows=1 width=4)
EXPLAIN
pictagev2=# show enable_seqscan;
NOTICE: enable_seqscan is on
SHOW VARIABLE
pictagev2=# set enable_seqscan=off;
SET VARIABLE
pictagev2=# explain select * from testBig where id1 = 1585 and id2 = 42;
NOTICE: QUERY PLAN:
Seq Scan on testbig (cost=100000000.00..100031634.08 rows=1 width=4)
EXPLAIN
This ends up with the query:
select * from testBig where id1 = 1585 and id2 = 42;
taking about 6 seconds, and I suspect it should be MUCH faster with an
"Index Scan", specially when the whole primary key is specified.
For what I read in section 11.1 ("Using EXPLAIN") of the users guide, it
SHOULD be using "Index Scan" instead of "Seq Scan".
This query is issued a lot of times in my coode, so having it take 6
seconds each time is unacceptable. Could someone PLEASE explain me how
to fix this?
Thanks a lot!
From | Date | Subject | |
---|---|---|---|
Next Message | Artigas, Ricardo Y. | 2002-03-14 03:08:59 | Re: Standby databases |
Previous Message | Stephan Szabo | 2002-03-14 03:04:28 | Re: Unexplainable slow down... |