pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Andrei Bintintan <klodoma@ar-sd.net>
Cc: pgsql-admin@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: Index not used. WHY?
Date: Thu, 4 Dec 2003 07:19:49 -0800 (PST)
Message-ID: <20031204071432.R66123@megazone.bigpanda.com> (raw)
In-Reply-To: <014901c3ba76$ffd65650$0b00a8c0@andy>
References: <014901c3ba76$ffd65650$0b00a8c0@andy>


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?




view thread (5+ messages)  latest in thread

Message-ID: <20031204071432.R66123@megazone.bigpanda.com>
Permalink:  ../20031204071432.R66123@megazone.bigpanda.com/
Also on:    postgresql.org/message-id/20031204071432.R66123@megazone.bigpanda.com

 · 

reply

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: sszabo@megazone.bigpanda.com, klodoma@ar-sd.net, pgsql-admin@postgresql.org
  Subject: Re: Index not used. WHY?
  In-Reply-To: <20031204071432.R66123@megazone.bigpanda.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