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

Re: Query on postgresql 7.4.2 not using index

From: Arnau <arnaulist(at)andromeiberica(dot)com>
To: chris smith <dmagick(at)gmail(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:49:33
Message-ID: 444E28ED.3090900@andromeiberica.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Guillaume SmetDate: 2006-04-25 14:31:51
Subject: Re: Query on postgresql 7.4.2 not using index
Previous:From: chris smithDate: 2006-04-25 13:34:24
Subject: Re: Query on postgresql 7.4.2 not using index

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