There are around 700 rows in this table.
If I set enable_seqscan=off then the index is used and I also used Vacuum
Analyze recently.
I find it strange because the number of values of id_user and id_modull are
somehow in the same distribution and when I search the table the id_user
index is used but the id_modull index is not used.
Does somehow postgre know that a seq scan runs faster in this case as a
index scan? Should I erase this index?
I have to say that the data's in this table are not changed offen, but there
are a LOT of joins made with this table.
Best regards.
Andy.
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: "Andrei Bintintan" <klodoma@ar-sd.net>
Cc: <pgsql-admin@postgresql.org>; <pgsql-performance@postgresql.org>
Sent: Thursday, December 04, 2003 5:19 PM
Subject: Re: [ADMIN] Index not used. WHY?
>
> 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?
>
