Skip site navigation (1) Skip section navigation (2)

Secondary indexes

From: Leandro Fanzone <leandro(at)hasar(dot)com>
To: postgreSQL <pgsql-novice(at)postgresql(dot)org>
Subject: Secondary indexes
Date: 2001-02-08 18:52:14
Message-ID: 3A82EADD.82277F2A@hasar.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hello. I have a table with, say, three fields. The first is the ID (integer,
unique, primary index). The second is an optional index that sometimes can be
blank, or even duplicated, a varchar(13). The third one is the data I want to
retrieve, it has no importance in this problem. I need to access sometimes by the
ID and sometimes by the secondary key, so I made an index using

CREATE INDEX my_index_name ON MY_TABLE(my_secondary_id);

When I select using the primary key, obviously uses the index created by default.
When I select using the secondary key, it EXPLAINs me that it would use sequencial
scan instead of the index I created, thus this search becomes extremely slow. Why
the engine would ignore the index? Do I have to make it "available" in some way I
am not aware of? I VACUUM ANALYZEd already, and nothing changed.
I thank you in advance any help.

Leandro.






In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2001-02-08 19:22:04
Subject: Re: Secondary indexes
Previous:From: Etienne De KerckhoveDate: 2001-02-07 21:23:59
Subject: UPDATES

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group