X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 410D911F624C for ; Tue, 25 Apr 2006 10:49:46 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 18790-03 for ; Tue, 25 Apr 2006 10:49:35 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from fafnir.androme.com (fafnir.androme.com [62.58.96.158]) by postgresql.org (Postfix) with ESMTP id 0CF8211F622B for ; Tue, 25 Apr 2006 10:49:33 -0300 (ADT) Received: by fafnir.androme.com (Postfix, from userid 1003) id 610F8B915EC; Tue, 25 Apr 2006 15:49:34 +0200 (CEST) Message-ID: <444E28ED.3090900@andromeiberica.com> Date: Tue, 25 Apr 2006 15:49:33 +0200 From: Arnau Reply-To: arnaulist@andromeiberica.com User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716) X-Accept-Language: en-us, en MIME-Version: 1.0 To: chris smith CC: pgsql-performance@postgresql.org Subject: Re: Query on postgresql 7.4.2 not using index References: <444E240D.3050000@andromeiberica.com> <3c1395330604250634r6f215ceame157f3dd5f5f4fa3@mail.gmail.com> In-Reply-To: <3c1395330604250634r6f215ceame157f3dd5f5f4fa3@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200604/546 X-Sequence-Number: 18630 chris smith wrote: > On 4/25/06, Arnau 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