X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 47BA8D1B535 for ; Wed, 12 Nov 2003 18:28:37 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 40876-03 for ; Wed, 12 Nov 2003 14:28:07 -0400 (AST) Received: from shire.ontko.com (shire.ontko.com [199.164.165.1]) by svr1.postgresql.org (Postfix) with ESMTP id 40BCFD1B522 for ; Wed, 12 Nov 2003 14:28:06 -0400 (AST) Received: from nick (bilbo.ontko.com [199.164.165.101]) by shire.ontko.com (8.12.3/8.12.3/Debian-6.6) with SMTP id hACIS45f017833; Wed, 12 Nov 2003 13:28:05 -0500 Reply-To: From: "Nick Fankhauser" To: "Tom Lane" Cc: "Manfred Koizar" , "Pgsql-Performance@Postgresql. Org" Subject: Re: Seeking help with a query that takes too long Date: Wed, 12 Nov 2003 13:27:53 -0500 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) In-Reply-To: <10144.1068657035@sss.pgh.pa.us> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/74 X-Sequence-Number: 4634 > You might have to resort to brute force, like "set enable_nestloop=false". > Just out of curiosity, what do you get if you do that? I get a different plan, but similar execution time: Limit (cost=323437.13..323437.13 rows=1 width=115) (actual time=170921.89..170922.95 rows=1000 loops=1) -> Sort (cost=323437.13..323437.13 rows=1 width=115) (actual time=170921.89..170922.26 rows=1001 loops=1) Sort Key: max((actor.actor_full_name)::text), count(case_data.case_id) -> Aggregate (cost=323437.08..323437.12 rows=1 width=115) (actual time=170849.94..170898.06 rows=3457 loops=1) -> Group (cost=323437.08..323437.09 rows=3 width=115) (actual time=170849.90..170873.60 rows=5880 loops=1) -> Sort (cost=323437.08..323437.08 rows=3 width=115) (actual time=170847.97..170850.21 rows=5880 loops=1) Sort Key: actor.actor_id -> Hash Join (cost=253333.29..323437.06 rows=3 width=115) (actual time=122873.80..170814.27 rows=5880 loops=1) Hash Cond: ("outer".case_id = "inner".case_id) -> Seq Scan on case_data (cost=0.00..60368.16 rows=1947116 width=39) (actual time=12.95..43542.25 rows=1947377 loops=1) -> Hash (cost=253333.28..253333.28 rows=3 width=76) (actual time=122844.40..122844.40 rows=0 loops=1) -> Hash Join (cost=6.02..253333.28 rows=3 width=76) (actual time=24992.70..122810.32 rows=5883 loops=1) Hash Cond: ("outer".actor_id = "inner".actor_id) -> Seq Scan on actor_case_assignment (cost=0.00..209980.49 rows=8669349 width=34) (actual time=9.13..85504.05 rows=8670467 loops=1) -> Hash (cost=6.01..6.01 rows=1 width=42) (actual time=24926.56..24926.56 rows=0 loops=1) -> Index Scan using actor_full_name_uppercase on actor (cost=0.00..6.01 rows=1 width=42) (actual time=51.67..24900.53 rows=3502 loops=1) Index Cond: ((actor_full_name_uppercase >= 'SANDERS'::character varying) AND (actor_full_name_uppercase < 'SANDERT'::character varying)) Filter: (actor_full_name_uppercase ~~ 'SANDERS%'::text) Total runtime: 170925.93 msec (19 rows) -Nick