Skip site navigation (1) Skip section navigation (2)

Query on postgresql 7.4.2 not using index

From: Arnau <arnaulist(at)andromeiberica(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Query on postgresql 7.4.2 not using index
Date: 2006-04-25 13:28:45
Message-ID: 444E240D.3050000@andromeiberica.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi all,

   I have the following running on postgresql version 7.4.2:

CREATE SEQUENCE agenda_user_group_id_seq
MINVALUE 1
MAXVALUE 9223372036854775807
CYCLE
INCREMENT 1
START 1;

CREATE TABLE AGENDA_USERS_GROUPS
(
  AGENDA_USER_GROUP_ID  INT8
                        CONSTRAINT pk_agndusrgrp_usergroup PRIMARY KEY
                        DEFAULT NEXTVAL('agenda_user_group_id_seq'),
  USER_ID               NUMERIC(10)
                        CONSTRAINT fk_agenda_uid  REFERENCES 
AGENDA_USERS (USER_ID)
                        ON DELETE CASCADE
                        NOT NULL,
  GROUP_ID              NUMERIC(10)
                        CONSTRAINT fk_agenda_gid  REFERENCES 
AGENDA_GROUPS (GROUP_ID)
                        ON DELETE CASCADE
                        NOT NULL,
  CREATION_DATE         DATE
                        DEFAULT CURRENT_DATE,
                        CONSTRAINT un_agndusrgrp_usergroup 
UNIQUE(USER_ID, GROUP_ID)
);

CREATE INDEX i_agnusrsgrs_userid ON AGENDA_USERS_GROUPS ( USER_ID );
CREATE INDEX i_agnusrsgrs_groupid ON AGENDA_USERS_GROUPS ( GROUP_ID );


When I execute:

EXPLAIN ANALYZE SELECT agenda_user_group_id FROM agenda_users_groups 
WHERE group_id = 9;

it does a sequential scan and doesn't use the index and I don't 
understand why, any idea? I have the same in postgresql 8.1 and it uses 
the index :-|

Thanks
-- 
Arnau

Responses

pgsql-performance by date

Next:From: chris smithDate: 2006-04-25 13:34:24
Subject: Re: Query on postgresql 7.4.2 not using index
Previous:From: Richard HuxtonDate: 2006-04-25 10:40:13
Subject: Re: security for row level but not based on Database user's

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group