Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id D82EC63404F for ; Mon, 16 Mar 2009 10:04:58 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 15541-08-2 for ; Mon, 16 Mar 2009 10:04:47 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from yx-out-2324.google.com (yx-out-2324.google.com [74.125.44.28]) by mail.postgresql.org (Postfix) with ESMTP id 1677D6337A1 for ; Mon, 16 Mar 2009 10:04:19 -0300 (ADT) Received: by yx-out-2324.google.com with SMTP id 3so1614869yxj.73 for ; Mon, 16 Mar 2009 06:04:18 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:cc:content-type; bh=1CYP2+I8faMV46QWd4Rhln7Z55VJfkbr89Qd/4SCNb0=; b=GrMB1Uq+5zd+146+sKDzD6qj6gt7sV1hR8MsM7iJUzjd6fCYwLeaqchKeP4FoCATd7 lbu5U6qFInY/MMHzDvGK9a8p4OYRppEQvoJVKopvfsDBFyNoAyJirWnOxfyDWP2Nwg6P DikvBS87AzpntT6i0qnSNP0KXIEavObzEUuc8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:cc :content-type; b=IR/lh9sShkftk6S3mnbF6+zvj4PhSc9V8NPCeM9pvbKAcqokkeUGEDtoTkGKlsCV0o Ujq7uSA0o+bBrlzC7yjl0lq/wuk02mbb3xod53n8+ukT4ta1v2fZuGTC7S6wSmZKlyv4 3o8YLuXLoaL1IZJtBNCCRJgvmpGhnrjWWChBo= MIME-Version: 1.0 Received: by 10.231.20.3 with SMTP id d3mr987908ibb.18.1237208658200; Mon, 16 Mar 2009 06:04:18 -0700 (PDT) In-Reply-To: References: <49B51B60.6030600@megafon.hr> <27649.1236616294@sss.pgh.pa.us> <355.1236705647@sss.pgh.pa.us> Date: Mon, 16 Mar 2009 15:04:18 +0200 Message-ID: <331e40660903160604v3a47fabfqef2bdd75289edb4e@mail.gmail.com> Subject: Re: Query much slower when run from postgres function From: =?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=000325574a4e95ca9a04653c16e2 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.582 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001, MISSING_HEADERS=1.581 X-Spam-Level: * X-Archive-Number: 200903/202 X-Sequence-Number: 33124 --000325574a4e95ca9a04653c16e2 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 2009/3/14 decibel > On Mar 10, 2009, at 12:20 PM, Tom Lane wrote: > >> fche@redhat.com (Frank Ch. Eigler) writes: >> >>> For a prepared statement, could the planner produce *several* plans, >>> if it guesses great sensitivity to the parameter values? Then it >>> could choose amongst them at run time. >>> >> >> We've discussed that in the past. "Choose at runtime" is a bit more >> easily said than done though --- you can't readily flip between plan >> choices part way through, if you've already emitted some result rows. >> > > True, but what if we planned for both high and low cardinality cases, > assuming that pg_stats indicated both were a possibility? We would have to > store multiple plans for one prepared statement, which wouldn't work well > for more complex queries (if you did high and low cardinality estimates for > each table you'd end up with 2^r plans, where r is the number of relations), > so we'd need a way to cap it somehow. Of course, whether that's easier than > having the ability to throw out a current result set and start over with a > different plan is up for debate... > > On a related note, I wish there was a way to tell plpgsql not to pre-plan a > query. Sure, you can use EXECUTE, but building the query plan is a serious > pain in the rear. > I'd say it would be great for PostgreSQL to replan each execution of query automatically if execution plan tells it would take some factor (say, x100, configurable) more time to execute query then to plan. In this case it would not spend many time planning for small queries, but will use the most efficient plan possible for long queries. And even if a query can't be run better, it would spend only 1/factor time more (1% more time for factor of 100). --000325574a4e95ca9a04653c16e2 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

2009/3/14 decibel <= decibel@decibel.org>
On Mar 10, 2009, at 12:20 PM, Tom Lane wrote:
fche@redhat.com (F= rank Ch. Eigler) writes:
For a prepared statement, could the planner produce *several* plans,
if it guesses great sensitivity to the parameter values? =A0Then it
could choose amongst them at run time.

We've discussed that in the past. =A0"Choose at runtime" is a= bit more
easily said than done though --- you can't readily flip between plan choices part way through, if you've already emitted some result rows.

True, but what if we planned for both high and low cardinality cases, assum= ing that pg_stats indicated both were a possibility? We would have to store= multiple plans for one prepared statement, which wouldn't work well fo= r more complex queries (if you did high and low cardinality estimates for e= ach table you'd end up with 2^r plans, where r is the number of relatio= ns), so we'd need a way to cap it somehow. Of course, whether that'= s easier than having the ability to throw out a current result set and star= t over with a different plan is up for debate...

On a related note, I wish there was a way to tell plpgsql not to pre-plan a= query. Sure, you can use EXECUTE, but building the query plan is a serious= pain in the rear.

I'd say= it would be great for PostgreSQL to replan each execution of query automat= ically if execution plan tells it would take some factor (say, x100, config= urable) more time to execute query then to plan. In this case it would not = spend many time planning for small queries, but will use the most efficient= plan possible for long queries. And even if a query can't be run bette= r, it would spend only 1/factor time more (1% more time for factor of 100).=
--000325574a4e95ca9a04653c16e2--