Re: Query on postgresql 7.4.2 not using index

Arnau <arnaulist@xxxxxxxxxxxxxxxxxx> · Tue, 25 Apr 2006 15:49:33 +0200

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

 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