Pg uses non-unique index instead of pkey index

From: "Thalis A(dot) Kalfigopoulos" <thalis(at)cs(dot)pitt(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: Pg uses non-unique index instead of pkey index
Date: 2001-06-25 19:41:27
Message-ID: Pine.LNX.4.21.0106251534340.27771-100000@aluminum.cs.pitt.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

EXPLAIN'ing the very simple query:
SELECT * FROM experimentsc WHERE expid=12;

I get the following plan:
NOTICE: QUERY PLAN:

Index Scan using experimentsc_expid_i on experimentsc (cost=0.00..2.01 rows=1 width=44)

EXPLAIN

I have two indeces on the same thing: expid (don't as why :^) One is the index formed because it is the primary key, and the other I created manually.

test=# \di expe
List of relations
Name | Type | Owner
------------------------+-------+--------
experimentsc_expid_i | index | thalis
experimentsc_expname_i | index | thalis
experimentsc_pkey | index | pgsql
(3 rows)

test=# \d experimentsc_expid_i
Index "experimentsc_expid_i"
Attribute | Type
-----------+---------
expid | integer
btree

test=# \d experimentsc_pkey
Index "experimentsc_pkey"
Attribute | Type
-----------+---------
expid | integer
unique btree (primary key)

Why exactly is experimentsc_expid_i chosen over experimentsc_pkey? Not that it would make any difference in this case, but I'd guess that an equality condition on the pkey should go for the corresponding index.

TIA,
thalis

ps hopefully not because it comes first in lexicographic ordering ;-)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thalis A. Kalfigopoulos 2001-06-25 19:50:51 Re: Red Hat to support PostgreSQL
Previous Message Peter Eisentraut 2001-06-25 19:40:55 Re: PostgreSQL: YMMV?