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

Re: Bind message

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Markus Schaber <schabi(at)logix-tt(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Bind message
Date: 2006-09-26 13:10:38
Message-ID: 24245.1159276238@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-jdbc
Markus Schaber <schabi(at)logix-tt(dot)com> writes:
> Don't forget that, in the current implementation, the query planner has
> no choice but planning the query without any actual parameter values,
> which is likely to give equal or worse results in most cases.

No, I think you are oversimplifying.  What the planner is supposed to do
when given a parameterized query (and no parameter value info) is to
generate a plan that won't be too terribly awful regardless of the
parameter values.  With a set of sample values, it may generate a plan
that is great for those values and utterly unusable for anything else.
Here's a simple example:

	select ... from a join b on (a.key = b.key) where a.val = ?

Given a parameter value that looks like it will match only one A row,
you will likely get a plan like this:

	Nest Loop
		Index Scan on A
			Index Cond: val = ?
		Index Scan on B
			Index Cond: b.key = a.key

which is about as fast as you can get if indeed there's only one match.
However if the parameter value matches very many A rows, this plan is
horrid.  With no value for the parameter, the planner should pick a
compromise plan (perhaps a mergejoin) that may not be as fast for the
single-match case, but will finish before doomsday in the other case.

			regards, tom lane

In response to

Responses

pgsql-jdbc by date

Next:From: Markus SchaberDate: 2006-09-26 13:20:03
Subject: Re: Bind message
Previous:From: till toengesDate: 2006-09-26 13:07:12
Subject: Re: bytea memory improvement

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