Select not using primary key index

Started by Sergio Freueabout 24 years ago5 messagesgeneral
Jump to latest
#1Sergio Freue
sfreue@yahoo.com

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!

#2Doug McNaught
doug@wireboard.com
In reply to: Sergio Freue (#1)
Re: Select not using primary key index

Sergio Freue <sfreue@yahoo.com> writes:

I'm trying to run a simple query on a big (1,500,000 rows) table and
I'm getting bad performance.

What version are you using? 7.2 keeps much better statistics for query
planning than 7.1.

-Doug
--
Doug McNaught Wireboard Industries http://www.wireboard.com/

Custom software development, systems and network consulting.
Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD...

#3Stephan Szabo
sszabo@megazone23.bigpanda.com
In reply to: Sergio Freue (#1)
Re: Select not using primary key index

On Thu, 14 Mar 2002, Sergio Freue wrote:

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));

You'll need to either explicitly cast the constants to smallint
or single quote them to get them to match up in type. There've been
past discussions on this in the mailing lists if you want more info.

#4Carl van Tast
vanTast@Pivot.at
In reply to: Sergio Freue (#1)
Re: Select not using primary key index

On Thu, 14 Mar 2002 00:07:54 -0300, Sergio Freue <sfreue@yahoo.com>
wrote:

create table testBig (id1 smallint not null,id2 smallint not
null,primary key (id1, id2));

Add about 1,500,000 records
[...]
select * from testBig where id1 = 1585 and id2 = 42;

select *
from testBig
where id1 = 1585::smallint and id2 = 42::smallint;

HTH,
Carl van Tast

#5Sergio Freue
sfreue@yahoo.com
In reply to: Sergio Freue (#1)
Re: Select not using primary key index

Carl van Tast wrote:

On Thu, 14 Mar 2002 00:07:54 -0300, Sergio Freue <sfreue@yahoo.com>
wrote:

create table testBig (id1 smallint not null,id2 smallint not
null,primary key (id1, id2));

Add about 1,500,000 records
[...]
select * from testBig where id1 = 1585 and id2 = 42;

select *
from testBig
where id1 = 1585::smallint and id2 = 42::smallint;

HTH,
Carl van Tast

THANKS!!! That did it!

Is there any configuration parameter I can alter to get the same result?

Sergio Freue