From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Andrei Bintintan <klodoma(at)ar-sd(dot)net> |
Cc: | pgsql-admin(at)postgresql(dot)org, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Index not used. WHY? |
Date: | 2003-12-04 15:19:49 |
Message-ID: | 20031204071432.R66123@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-performance |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2003-12-04 15:36:27 | Re: Trigger to call an external program |
Previous Message | Alan Graham | 2003-12-04 15:13:10 | Trigger to call an external program |
From | Date | Subject | |
---|---|---|---|
Next Message | Jack Coates | 2003-12-04 16:06:23 | tuning questions |
Previous Message | Andrei Bintintan | 2003-12-04 14:57:51 | Index not used. WHY? |