Skip site navigation (1) Skip section navigation (2)

Re: Avoiding bad prepared-statement plans.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Mark Mielke <mark(at)mark(dot)mielke(dot)cc>
Cc: Jeroen Vermeulen <jtv(at)xs4all(dot)nl>, Alex Hunsaker <badalex(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Greg Stark <gsstark(at)mit(dot)edu>, Bart Samwel <bart(at)samwel(dot)tk>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Avoiding bad prepared-statement plans.
Date: 2010-02-26 20:47:55
Message-ID: 4127.1267217275@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Mark Mielke <mark(at)mark(dot)mielke(dot)cc> writes:
> Here are parts that can be done "fixed":

> 1) Statement parsing and error checking.
> 2) Identification of tables and columns involved in the query.

The above two are done in the parser, not the planner.

> 3) Query the column statistics for involved columns, to be used in plan 
> cost estimation now and later.

What makes you think that these can be cached any more effectively than
they are now?

> 4) Determine plan constraints under which elements of the plan must be 
> executed a certain way (something like constant folding for a compiler), 
> or for which parameter substitution would not impact the outcome.

Some marginal savings achievable there, perhaps.  But you can't just
push const-folding upstream, because one of the purposes of it is to
const-fold expressions involving parameter values, if the planner is
allowed to assume the parameter values are known.  So while we could
do a pass of const-folding upstream (in the rewriter say), we would
still need a pass at plan time.  Not very clear that this nets out
as a win.

> 5) Identify the elements of the plan that still require plan enumeration 
> and plan selection, to be used in a later part of the pipeline.

[ shrug... ] In practice that will typically be "all of them".  The only
time it wouldn't be is when a parameter is only used in a join
condition, but I think the majority of practical cases have parameters
that are involved in relation scan conditions.  Even if you could cache
the path results for some of the relations involved in a query, the
required size of the cache would be staggering.

> And that all said, I think I am challenging the status quo and ticking 
> people off. So while my intent is to challenge the status quo, it is not 
> to tick people off. So, please let me know if you would like me to 
> continue, or if you have already written this off. :-)

It would be more productive if you spent awhile studying the code as it
actually exists now, and then started to propose rearchitecting.  What
I'm reading here seems to be largely uninformed speculation.

			regards, tom lane

In response to

pgsql-hackers by date

Next:From: Gokulakannan SomasundaramDate: 2010-02-26 21:01:58
Subject: Re: A thought on Index Organized Tables
Previous:From: Yeb HavingaDate: 2010-02-26 20:46:27
Subject: Re: Avoiding bad prepared-statement plans.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group