Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 2E4896327B9 for ; Mon, 1 Mar 2010 15:34:56 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 02676-07 for ; Mon, 1 Mar 2010 19:34:42 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ew0-f226.google.com (mail-ew0-f226.google.com [209.85.219.226]) by mail.postgresql.org (Postfix) with ESMTP id 3557E632261 for ; Mon, 1 Mar 2010 15:34:45 -0400 (AST) Received: by ewy26 with SMTP id 26so1545924ewy.3 for ; Mon, 01 Mar 2010 11:34:43 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from :user-agent:mime-version:to:cc:subject:references:in-reply-to :content-type:content-transfer-encoding; bh=UJ388Y3qWcEQsVSwdXzLrt8rTBEKmDseh8c3K6unId8=; b=Dc6XpjhG+1bCH8dmbDRyqBAmgVuqXXC88MmYSW6YfIbK7C8IGdVHyNybEAXDBAmphR ptwIgAC4nH1xKaiRXoHvqyV2fKJhKaQCHfoyJpygUULZlzKpgwSw4HZ7D43fx1QD9MEa J3N/LYzfY5QDHIDUoUSwxyYSSfDJcg4bfUFcY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type:content-transfer-encoding; b=XgAo1iawA34ACpANhGE5gO96mFdeLoJPyDNODL+8clKeP5lvIdi48IbONYijZVCpgb +z/gRSpjkxUnGJLbI+ALXNk5C0sKEjIGWo/et4AVnuHL/ORAtNS1JVDlI0QylGPCKV1k eo+d6ZA5bFSDejMTzvfsxqmnrXIJ3AdOGnG94= Received: by 10.213.99.142 with SMTP id u14mr157536ebn.29.1267472082835; Mon, 01 Mar 2010 11:34:42 -0800 (PST) Received: from ?0.0.0.0? (5357DA75.cable.casema.nl [83.87.218.117]) by mx.google.com with ESMTPS id 16sm2527726ewy.7.2010.03.01.11.34.41 (version=TLSv1/SSLv3 cipher=RC4-MD5); Mon, 01 Mar 2010 11:34:42 -0800 (PST) Message-ID: <4B8C16CE.6090901@gmail.com> Date: Mon, 01 Mar 2010 20:34:38 +0100 From: Yeb Havinga User-Agent: Thunderbird 2.0.0.23 (Windows/20090812) MIME-Version: 1.0 To: Rainer Pruy CC: "pgsql-performance@postgresql.org" Subject: Re: Query slowing down significantly?? References: <4B8BB815.4060104@acrys.com> <17910.1267460132@sss.pgh.pa.us> <4B8BFDEF.1080006@acrys.com> In-Reply-To: <4B8BFDEF.1080006@acrys.com> Content-Type: text/plain; charset=ISO-8859-15; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.413 tagged_above=-10 required=5 tests=AWL=0.186, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201003/13 X-Sequence-Number: 37838 Rainer Pruy wrote: > Thanks for the hint. > I should have been considering that in the first place. > (But the obvious is easily left unrecognised..) > > The prepared statement gives: > > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------------------------------------------- > Nested Loop (cost=0.00..25.18 rows=2 width=175) (actual time=36.116..49.998 rows=1 loops=1) > -> Index Scan using x_context_01 on context c (cost=0.00..10.76 rows=2 width=67) (actual time=0.029..6.947 rows=12706 loops=1) > Index Cond: ((contextid)::text = $1) > -> Index Scan using x_fk_context_hierarchy_02 on context_hierarchy h (cost=0.00..7.20 rows=1 width=108) (actual time=0.003..0.003 > rows=0 loops=12706) > Index Cond: (h.contextidx = c.idx) > Filter: (((h.hierarchyname)::text = $2) AND (h.parentidx = $3)) > Total runtime: 50.064 ms > (7 rows) > > > And that is quite a bad plan given the current distribution of values. > Another approach might be to rewrite recursion into your hierarchy with the in 8.4 new WITH RECURSIVE option in sql queries. The possible gains there are way beyond anything you can accomplish with optimizing recursive functions. Regards, Yeb Havinga