Postgres Pro
Downloads
Home   >   mailing lists

Thread: Select not using primary key index

Select not using primary key index

From
Sergio Freue
Date:
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!

Re: Select not using primary key index

From
Doug McNaught
Date:
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...

Re: Select not using primary key index

From
Stephan Szabo
Date:
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.

Re: Select not using primary key index

From
Carl van Tast
Date:
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

Re: Select not using primary key index

From
Sergio Freue
Date:
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