X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 64D61D1B468 for ; Thu, 4 Dec 2003 14:55:53 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 44395-09 for ; Thu, 4 Dec 2003 10:55:26 -0400 (AST) Received: from ar-sd.net (unknown [81.196.32.112]) by svr1.postgresql.org (Postfix) with ESMTP id 105B6D1B432 for ; Thu, 4 Dec 2003 10:55:22 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by ar-sd.net (Postfix) with ESMTP id 7C9DD1D021 for ; Thu, 4 Dec 2003 16:58:37 +0200 (EET) Received: from andy (unknown [192.168.0.11]) by ar-sd.net (Postfix) with SMTP id 562D61CCD8 for ; Thu, 4 Dec 2003 16:58:37 +0200 (EET) Message-ID: <014901c3ba76$ffd65650$0b00a8c0@andy> From: "Andrei Bintintan" To: Subject: Index not used. WHY? Date: Thu, 4 Dec 2003 16:57:51 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0146_01C3BA87.C0BC67F0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4927.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4927.1200 X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.6 tagged_above=0.0 required=5.0 tests=HTML_50_60, HTML_FONT_COLOR_BLUE, HTML_FONT_COLOR_GREEN, HTML_FONT_COLOR_RED, HTML_FONT_COLOR_UNSAFE X-Spam-Level: X-Archive-Number: 200312/35 X-Sequence-Number: 11543 This is a multi-part message in MIME format. ------=_NextPart_000_0146_01C3BA87.C0BC67F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi,=20 I have the following table: CREATE TABLE public.rights ( id int4 DEFAULT nextval('"rights_id_seq"'::text) NOT NULL,=20 id_user int4 NOT NULL,=20 id_modull int4 NOT NULL,=20 CONSTRAINT rights_pkey PRIMARY KEY (id) )=20 and I created the following indexes: CREATE INDEX right_id_modull_idx ON rights USING btree (id_modull); CREATE INDEX right_id_user_idx ON rights USING btree (id_user); Now the problem: EXPLAIN SELECT * FROM rights r WHERE r.id_modull =3D15 returnes: Seq Scan on rights r (cost=3D0.00..12.30 rows=3D42 width=3D12) Filter: (id_modull =3D 15) EXPLAIN SELECT * FROM rights r WHERE r.id_user =3D15 returnes: Index Scan using right_id_user_idx on rights r (cost=3D0.00..8.35 rows=3D11= width=3D12) Index Cond: (id_user =3D 15) Question: Why the right_id_modull_idx is NOT USED at the 1st query and the = second query the right_id_user_idx index is used.=20 I don't understand this.=20 Thanx in advance. Andy. ------=_NextPart_000_0146_01C3BA87.C0BC67F0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
 
I have the following table:

CREATE TABLE public.rights (
id int4 DEFAULT=20 nextval('"rights_id_seq"'::text) NOT NULL,
= id_user int4 NOT NULL,
= id_modull int4 NOT NULL,
= CONSTRAINT rights_pkey PRIMARY KEY (id)
)=20

and I created the following indexes:<= /P>

CREATE INDEX right_id_modull_idx ON=20 rights USING btree (id_modull);
CREATE
INDEX right_id_u= ser_idx=20 ON rights USING b= tree=20 (id_user);

Now the problem:

EXPLAIN SELECT * FROM rights r WHERE r.id_modull =3D15
returnes:
Seq Scan on= =20 rights r (cost=3D0.00..12.30 rows=3D42 width=3D12)
Filter: (id_modull = =3D=20 15)

EXPLAIN SELECT * FROM rights r WHERE r.id_user =3D15
returnes:
Index Scan using right_id_user_idx on=20 rights r (cost=3D0.00..8.35 rows=3D11 width=3D12)
= Index=20 Cond: (id_user =3D 15)

Question: Why the right_id_modull_idx is NOT US= ED at=20 the 1st query and the second query the right_id_user_idx index is used.=20

I don't understand this.

Thanx in=20 advance.
Andy.

 

------=_NextPart_000_0146_01C3BA87.C0BC67F0--