Select not using primary key index

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!

Responses

Browse pgsql-general by date

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