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 BB9F1D1B4B1; Thu, 4 Dec 2003 15:20:16 +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 53565-08; Thu, 4 Dec 2003 11:19:49 -0400 (AST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id EBAEBD1B468; Thu, 4 Dec 2003 11:19:44 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 3E7E535500; Thu, 4 Dec 2003 07:19:49 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 3CE3935437; Thu, 4 Dec 2003 07:19:49 -0800 (PST) Date: Thu, 4 Dec 2003 07:19:49 -0800 (PST) From: Stephan Szabo To: Andrei Bintintan Cc: pgsql-admin@postgresql.org, pgsql-performance@postgresql.org Subject: Re: Index not used. WHY? In-Reply-To: <014901c3ba76$ffd65650$0b00a8c0@andy> Message-ID: <20031204071432.R66123@megazone.bigpanda.com> References: <014901c3ba76$ffd65650$0b00a8c0@andy> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/37 X-Sequence-Number: 11545 On Thu, 4 Dec 2003, Andrei Bintintan wrote: > Hi, > > I have the following table: > CREATE TABLE public.rights ( > id int4 DEFAULT nextval('"rights_id_seq"'::text) NOT NULL, > id_user int4 NOT NULL, > id_modull int4 NOT NULL, > CONSTRAINT rights_pkey PRIMARY KEY (id) > ) > > 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 =15 > returnes: > Seq Scan on rights r (cost=0.00..12.30 rows=42 width=12) > Filter: (id_modull = 15) > > EXPLAIN SELECT * FROM rights r WHERE r.id_user =15 > returnes: > Index Scan using right_id_user_idx on rights r (cost=0.00..8.35 rows=11 width=12) > Index Cond: (id_user = 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. As a note, pgsql-performance is a better list for these questions. So, standard questions: How many rows are in the table, what does EXPLAIN ANALYZE show for the queries, if you force index usage (set enable_seqscan=off) on the first what does EXPLAIN ANALYZE show then, have you used ANALYZE/VACUUM ANALYZE recently?