Re: Query on postgresql 7.4.2 not using index

chris smith wrote:
> 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.
>

The same, the table has 2547556 entries:

espsm_moviltelevision=# EXPLAIN ANALYZE SELECT agenda_user_group_id FROM
agenda_users_groups
espsm_moviltelevision-# WHERE group_id::int8 = 9;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Seq Scan on agenda_users_groups (cost=0.00..59477.34 rows=12738
width=8) (actual time=3409.541..11818.794 rows=367026 loops=1)
Filter: ((group_id)::bigint = 9)
Total runtime: 13452.114 ms
(3 filas)

espsm_moviltelevision=# EXPLAIN ANALYZE SELECT agenda_user_group_id FROM
agenda_users_groups
espsm_moviltelevision-# WHERE group_id = '9';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Seq Scan on agenda_users_groups (cost=0.00..53108.45 rows=339675
width=8) (actual time=916.903..5763.830 rows=367026 loops=1)
Filter: (group_id = 9::numeric)
Total runtime: 7259.861 ms
(3 filas)

espsm_moviltelevision=# select count(*) from agenda_users_groups ;
count
---------
2547556

Thanks
--
Arnau