Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id A5F022E02E6 for ; Sun, 27 Jan 2008 19:01:16 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 95373-05 for ; Sun, 27 Jan 2008 19:01:06 -0400 (AST) Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.152]) by postgresql.org (Postfix) with ESMTP id 50EF92E01AC for ; Sun, 27 Jan 2008 19:01:10 -0400 (AST) Received: by fg-out-1718.google.com with SMTP id 22so1500242fge.23 for ; Sun, 27 Jan 2008 15:01:09 -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:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=YGTkmojzSRJW/5U9LRsfa3r+d7MKgWT/k7kcdmTFrG4=; b=i10cgVFwGepiAX2Y7wZi8XlqHu9c6hJAC8MqFuQU72FH39D8q6uMj01AN1i+bIBJZm2AGugo9Mgkcw902s/qk9lKoAvm1GCUepV/hIhpxIf85UpyYPHDNtmJ3ew1u2hTxirYFSA9xwvugszNEkGJIl0Iar7OgmooRtm9Jzi0W/U= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=WnSn8NrHH9a5ClpO8x4Z1K+n/4qLBf/Sl2+xKpdFjrA6BlOny43MHTZcstJwvD2XaT0W5i+kMAI+D6UauZbI9ailosnZ4TVi4mJpbvQ1I7JFaqoF8ogCuNrAVWuGf6AEsec/S/iJzlscCuusqolN10/wZtM9eVDIR7gx6sodkmM= Received: by 10.78.201.10 with SMTP id y10mr6455605huf.11.1201474868996; Sun, 27 Jan 2008 15:01:08 -0800 (PST) Received: by 10.78.40.5 with HTTP; Sun, 27 Jan 2008 15:01:08 -0800 (PST) Message-ID: Date: Sun, 27 Jan 2008 18:01:08 -0500 From: "Merlin Moncure" To: "Dean Rasheed" Subject: Re: Slow set-returning functions Cc: "Tom Lane" , "Heikki Linnakangas" , pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <47935C0C.4090908@enterprisedb.com> <10405.1200853835@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/249 X-Sequence-Number: 28488 On Jan 27, 2008 12:29 PM, Dean Rasheed wrote: > >> Is there any way that I can see what execution plan is being used > >> internally by the functions? > >> > > > > Not directly, but you can do this: > > > > > > postgres=# PREPARE p (int4) AS SELECT id FROM foo WHERE lower(name) LIKE > > 'foo' ORDER BY id OFFSET 0 LIMIT $1; > > PREPARE > > > > postgres=# EXPLAIN EXECUTE p(100); QUERY PLAN > > ----------------------------------------------------------------------------- > > Limit (cost=0.00..49.18 rows=2 width=4) > > > > -> Index Scan using foo_pkey on foo (cost=0.00..614.77 rows=25 width=4) > > Filter: (lower(name) ~~ 'foo'::text) > > (3 rows) > > > I think that having the ability to see the execution plans being used > by queries inside functions would be quite useful. > > More generally, I would like to be able to log the execution plans of > all queries issued by an application (in my case I am working on a web > application, where some of the queries are auto-generated by > Hibernate). I've tried setting debug_print_plan, but the results are a > little hard to interpret. > > As an experiment, I have tried hacking around a little with the code. > This is my first foray into the source code, so I might well be > missing something, but basically I added a new configuration parameter > debug_explain_plan which causes all queries to be instrumented and > ExecutorRun() to call explain_outNode() at the end, logging the > results at level DEBUG1. I read your email, blinked twice, and thought: where have you been all my life! :-) (IOW, +1) merlin