pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedYet another question about not use on indexes
3+ messages / 3 participants
[nested] [flat]
* Yet another question about not use on indexes
@ 2003-05-11 13:16 Xevi Serrats <tomcatbsd@yahoo.es>
2003-05-11 14:08 ` Re: Yet another question about not use on indexes Richard Huxton <dev@archonet.com>
2003-05-11 14:15 ` Re: Yet another question about not use on indexes Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: Xevi Serrats @ 2003-05-11 13:16 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Yet another question about not use on indexes
2003-05-11 13:16 Yet another question about not use on indexes Xevi Serrats <tomcatbsd@yahoo.es>
@ 2003-05-11 14:08 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2003-05-11 14:08 UTC (permalink / raw)
To: Xevi Serrats <tomcatbsd@yahoo.es>; pgsql-performance
On Sunday 11 May 2003 2:16 pm, Xevi Serrats wrote:
> Hi,
>
> I have created a table whit some indexes. I analize
> the query of this table and never use index.
> pfc=# \d document
> Table
> "public.document"
> Column | Type |
> Modifiers
> ------------+--------------------------+-----------------------------------
>----------------- codi | integer | not null
> default nextval('seq_document'::text)
> nom | character varying(32) | not null
etc...
> 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)
1. Have you done a VACUUM ANALYSE?
2. How many rows are in this table?
3. Can you post the output of EXPLAIN ANALYSE SELECT... - that actually runs
the query.
--
Richard Huxton
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Yet another question about not use on indexes
2003-05-11 13:16 Yet another question about not use on indexes Xevi Serrats <tomcatbsd@yahoo.es>
@ 2003-05-11 14:15 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2003-05-11 14:15 UTC (permalink / raw)
To: Xevi Serrats <tomcatbsd@yahoo.es>; +Cc: pgsql-performance
=?iso-8859-1?q?Xevi=20Serrats?= <tomcatbsd@yahoo.es> writes:
> 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)
Judging from the cost estimate, this table is too small to bother with
an indexscan.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-05-11 14:15 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-11 13:16 Yet another question about not use on indexes Xevi Serrats <tomcatbsd@yahoo.es>
2003-05-11 14:08 ` Richard Huxton <dev@archonet.com>
2003-05-11 14:15 ` Tom Lane <tgl@sss.pgh.pa.us>
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