Re: Query on postgresql 7.4.2 not using index

On 4/25/06, Arnau <arnaulist(at)andromeiberica(dot)com> 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/