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

Re: Query on postgresql 7.4.2 not using index

From: "chris smith" <dmagick(at)gmail(dot)com>
To: arnaulist(at)andromeiberica(dot)com
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query on postgresql 7.4.2 not using index
Date: 2006-04-25 13:34:24
Message-ID: 3c1395330604250634r6f215ceame157f3dd5f5f4fa3@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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/

In response to

Responses

pgsql-performance by date

Next:From: ArnauDate: 2006-04-25 13:49:33
Subject: Re: Query on postgresql 7.4.2 not using index
Previous:From: ArnauDate: 2006-04-25 13:28:45
Subject: Query on postgresql 7.4.2 not using index

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