X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id AEBE69FB1BD for ; Wed, 26 Jul 2006 14:23:11 -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 19004-10 for ; Wed, 26 Jul 2006 14:23:02 -0300 (ADT) X-Greylist: delayed 00:21:22.335716 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id C803A9FB0D1 for ; Wed, 26 Jul 2006 14:23:02 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from fafnir.androme.com (fafnir.androme.com [62.58.96.158]) by svr4.postgresql.org (Postfix) with ESMTP id 9A88A5AFB1A for ; Wed, 26 Jul 2006 17:01:38 +0000 (GMT) Received: by fafnir.androme.com (Postfix, from userid 1003) id 2C76FB91BB8; Wed, 26 Jul 2006 19:02:59 +0200 (CEST) Message-ID: <44C79FED.5080702@andromeiberica.com> Date: Wed, 26 Jul 2006 19:01:33 +0200 From: Arnau Reply-To: arnaulist@andromeiberica.com User-Agent: Thunderbird 1.5.0.4 (Windows/20060516) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Is it possible to speed this query up? Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.384 tagged_above=0 required=5 tests=SPF_SOFTFAIL X-Spam-Level: * X-Archive-Number: 200607/239 X-Sequence-Number: 20099 Hi all, I execute the following query on postgresql 8.1.0: SELECT u.telephone_number , u.telecom_operator_id , u.name FROM campanas_subcampaign AS sub , agenda_users AS u , agenda_users_groups ug WHERE sub.customer_app_config_id = 19362 AND sub.subcampaign_id = 9723 AND ug.agenda_user_group_id >= sub.ini_user_group_id AND ug.user_id=u.user_id AND ug.group_id IN ( SELECT group_id FROM campanas_groups WHERE customer_app_config_id = 19362 ) ORDER BY ug.agenda_user_group_id ASC LIMIT 150 the explain analyze shouts the following: Limit (cost=1.20..4600.56 rows=150 width=74) (actual time=76516.312..76853.191 rows=150 loops=1) -> Nested Loop (cost=1.20..333424.31 rows=10874 width=74) (actual time=76516.307..76852.896 rows=150 loops=1) -> Nested Loop (cost=1.20..299653.89 rows=10874 width=20) (actual time=76506.926..76512.608 rows=150 loops=1) Join Filter: ("outer".agenda_user_group_id >= "inner".ini_user_group_id) -> Nested Loop IN Join (cost=1.20..189802.77 rows=32623 width=20) (actual time=75938.659..76353.748 rows=16200 loops=1) Join Filter: ("outer".group_id = "inner".group_id) -> Index Scan using pk_agndusrgrp_usergroup on agenda_users_groups ug (cost=0.00..123740.26 rows=2936058 width=30) (actual time=0.101..61921.260 rows=2836638 loops=1) -> Materialize (cost=1.20..1.21 rows=1 width=10) (actual time=0.001..0.002 rows=1 loops=2836638) -> Seq Scan on campanas_groups (cost=0.00..1.20 rows=1 width=10) (actual time=0.052..0.053 rows=1 loops=1) Filter: (customer_app_config_id = 19362::numeric) -> Index Scan using pk_cmpnssubc_subcmpnid on campanas_subcampaign sub (cost=0.00..3.35 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=16200) Index Cond: (subcampaign_id = 9723) Filter: (customer_app_config_id = 19362::numeric) -> Index Scan using pk_agenda_uid on agenda_users u (cost=0.00..3.09 rows=1 width=78) (actual time=2.262..2.264 rows=1 loops=150) Index Cond: ("outer".user_id = u.user_id) Total runtime: 76853.504 ms (16 rows) Do you think I could do anything to speed it up? Cheers!! -- Arnau