There are around 700 rows in this table.
If I set enable_seqscan=off then the index is used and I also used Vacuum
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.
----- Original Message -----
From: "Stephan Szabo" <[EMAIL PROTECTED]>
To: "Andrei Bintintan" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
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
> > 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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly