From: Xevi Serrats <tomcatbsd@yahoo.es>
To: pgsql-performance@postgresql.org
Subject: Yet another question about not use on indexes
Date: Sun, 11 May 2003 15:16:00 +0200 (CEST)
Message-ID: <20030511131600.47686.qmail@web13001.mail.yahoo.com> (raw)
Hi,
I have created a table whit some indexes. I analize
the query of this table and never use index.
After this, I create a more simplistic table with two
columns and one index and the query uses the index.
Look at this:
pfc=# \d document
Table
"public.document"
Column | Type |
Modifiers
------------+--------------------------+----------------------------------------------------
codi | integer | not null
default nextval('seq_document'::text)
nom | character varying(32) | not null
descripcio | text |
formulari | integer |
fitxer | character varying(32) |
tamany | integer | default -1
data | timestamp with time zone | default
('now'::text)::timestamp(6) with time zone
Indexes: document_pkey primary key btree (codi),
ind_doc1 btree (codi),
ind_document btree (formulari)
Triggers: RI_ConstraintTrigger_19414,
RI_ConstraintTrigger_19418,
RI_ConstraintTrigger_19419,
actualitzaritemcercadocument,
altaitemcercadocument,
baixaitemcercadocument,
eliminaracldocument,
eliminaravaluaciodocument
pfc=# explain select * from document where codi=2;
QUERY PLAN
----------------------------------------------------------
Seq Scan on document (cost=0.00..1.19 rows=1
width=120)
Filter: (codi = 2)
(2 rows)
This query must use index document_pkey but explain
tells us that the query does a Sequencial scan on
table document.
Look at this simplistic case:
pfc=# \d prova
Table "public.prova"
Column | Type | Modifiers
--------+-----------------------+-----------
codi | integer | not null
nom | character varying(30) |
Indexes: prova_pkey primary key btree (codi)
pfc=# explain select * from prova where codi=1234;
QUERY PLAN
-------------------------------------------------------------------------
Index Scan using prova_pkey on prova
(cost=0.00..5.99 rows=1 width=37)
Index Cond: (codi = 1234)
(2 rows)
Now the query uses index, explain tell something about
index scan using index prova_pkey.
What is the diference with two cases? What must I do?
It is a bug? I need do something else?
Thanks a lot for helping me.
Regards,
Xevi.
_______________________________________________________________
Yahoo! Messenger
Nueva versión: Webcam, voz, y mucho más ¡Gratis!
Descárgalo ya desde http://messenger.yahoo.es
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: tomcatbsd@yahoo.es
Subject: Re: Yet another question about not use on indexes
In-Reply-To: <20030511131600.47686.qmail@web13001.mail.yahoo.com>
References: <20030511131600.47686.qmail@web13001.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox