X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E60F9476022 for ; Sun, 11 May 2003 09:15:57 -0400 (EDT) Received: from web13001.mail.yahoo.com (web13001.mail.yahoo.com [216.136.174.11]) by postgresql.org (Postfix) with SMTP id E23BC4758F1 for ; Sun, 11 May 2003 09:15:56 -0400 (EDT) Message-ID: <20030511131600.47686.qmail@web13001.mail.yahoo.com> Received: from [80.58.43.235] by web13001.mail.yahoo.com via HTTP; Sun, 11 May 2003 15:16:00 CEST Date: Sun, 11 May 2003 15:16:00 +0200 (CEST) From: =?iso-8859-1?q?Xevi=20Serrats?= Subject: Yet another question about not use on indexes To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Spam-Status: No, hits=-5.8 required=5.0 tests=BAYES_10 version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/121 X-Sequence-Number: 1900 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=3D# \d document Table "public.document" Column | Type |=20=20=20=20=20=20=20=20=20=20=20= =20=20=20 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=3D# explain select * from document where codi=3D2; QUERY PLAN ---------------------------------------------------------- Seq Scan on document (cost=3D0.00..1.19 rows=3D1 width=3D120) Filter: (codi =3D 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=3D# \d prova Table "public.prova" Column | Type | Modifiers --------+-----------------------+----------- codi | integer | not null nom | character varying(30) | Indexes: prova_pkey primary key btree (codi) pfc=3D# explain select * from prova where codi=3D1234; QUERY PLAN ------------------------------------------------------------------------- Index Scan using prova_pkey on prova=20 (cost=3D0.00..5.99 rows=3D1 width=3D37) Index Cond: (codi =3D 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?=20 Thanks a lot for helping me. Regards, Xevi. _______________________________________________________________ Yahoo! Messenger Nueva versi=F3n: Webcam, voz, y mucho m=E1s =A1Gratis!=20 Desc=E1rgalo ya desde http://messenger.yahoo.es