Re: Query on postgresql 7.4.2 not using index

["chris smith" <dmagick@xxxxxxxxx>]

On 4/25/06, Arnau <arnaulist@xxxxxxxxxxxxxxxxxx> wrote:
> 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;

Try

EXPLAIN ANALYZE SELECT agenda_user_group_id FROM agenda_users_groups
WHERE group_id::int8 = 9;

or

EXPLAIN ANALYZE SELECT agenda_user_group_id FROM agenda_users_groups
WHERE group_id = '9';

and let us know what happens.

--
Postgresql & php tutorials
http://www.designmagick.com/