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

Re: How embarrassing: optimization of a one-shot query doesn't work

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgreSQL(dot)org, pgsql-jdbc(at)postgreSQL(dot)org
Subject: Re: How embarrassing: optimization of a one-shot query doesn't work
Date: 2008-05-23 10:20:45
Message-ID: CE33CCC7-61C3-4D34-BED9-D8D884FC14AA@fastcrypt.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-jdbc
Tom,

I believe this is pretty much a show stopper for anyone using jdbc to  
upgrade to 8.3.x.

Any word on 8.3.2 ?

Dave
On 31-Mar-08, at 7:26 PM, Tom Lane wrote:

> While testing the changes I was making to Pavel's EXECUTE USING patch
> to ensure that parameter values were being provided to the planner,
> it became painfully obvious that the planner wasn't actually *doing*
> anything with them.  For example
>
> 	execute 'select count(*) from foo where x like $1' into c using $1;
>
> wouldn't generate an indexscan when $1 was of the form 'prefix%'.
>
> Some investigation showed that the planner is using the passed values
> for estimation purposes, but not for any purposes where the value  
> *must*
> be correct (not only this LIKE-optimization, but constraint exclusion,
> for instance).  The reason is that the parameter values are made
> available to estimate_expression_value but not to  
> eval_const_expressions.
> This is a thinko in a cleanup patch I made early in 8.3 development:
> http://archives.postgresql.org/pgsql-committers/2007-02/msg00352.php
> I said to myself "eval_const_expressions doesn't need any context,
> because a constant expression's value must be independent of context,
> so I can avoid changing its API".  Silly me.
>
> The implication of this is that 8.3 is significantly worse than 8.2
> in optimizing unnamed statements in the extended-Query protocol;
> a feature that JDBC, at least, relies on.
>
> The fix is simple: add PlannerInfo to eval_const_expressions's
> parameter list, as was done for estimate_expression_value.  I am
> slightly hesitant to do this in a stable branch, since it would break
> any third-party code that might be calling that function.  I doubt  
> there
> is currently any production-grade code doing so, but if anyone out  
> there
> is actively using those planner hooks we put into 8.3, it's  
> conceivable
> this would affect them.
>
> Still, the performance regression here is bad enough that I think  
> there
> is little choice.  Comments/objections?
>
> 			regards, tom lane
>
> -- 
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc


In response to

Responses

pgsql-hackers by date

Next:From: Heikki LinnakangasDate: 2008-05-23 12:48:02
Subject: Re: [HACKERS] Error while executing pg_dump "invalid memory alloc request size 4294967293"
Previous:From: Thomas H.Date: 2008-05-23 09:51:52
Subject: Re: BUG #4186: set lc_messages does not work

pgsql-jdbc by date

Next:From: Tom LaneDate: 2008-05-23 13:20:21
Subject: Re: How embarrassing: optimization of a one-shot query doesn't work
Previous:From: Albretch MuellerDate: 2008-05-21 20:46:41
Subject: Types of SQL statements swallowed by PG's engine

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