Re: Query on postgresql 7.4.2 not using index - Mailing list pgsql-performance

chris smith wrote:
> On 4/25/06, Arnau <arnaulist@andromeiberica.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