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

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

From: Michael Paesold <mpaesold(at)gmx(dot)at>
To: Dave Cramer <pg(at)fastcrypt(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgreSQL(dot)org, pgsql-jdbc(at)postgreSQL(dot)org
Subject: Re: [HACKERS] How embarrassing: optimization of a one-shot query doesn't work
Date: 2008-04-01 14:21:34
Message-ID: 0D9AE381-E186-446B-BFDF-2E82775BE4B3@gmx.at (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-jdbc
Am 01.04.2008 um 13:14 schrieb Dave Cramer:
>
> On 1-Apr-08, at 6:25 AM, Michael Paesold wrote:
>
>>
>> Am 01.04.2008 um 01:26 schrieb Tom Lane:
>>> 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%'.
>> ...
>>> 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?
>>
>> Yeah, please fix this performance regression in the 8.3 branch.  
>> This would affect most of the JDBC applications out there, I think.
>>
> Was the driver ever changed to take advantage of the above strategy?

IIRC, it is used in most cases with the v3 protocol, as long as you  
don't set a prepare-threshold.

Best Regards
Michael Paesold

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2008-04-01 14:22:03
Subject: Re: [HACKERS] ANALYZE getting dead tuple count hopelessly wrong
Previous:From: Morris GoldsteinDate: 2008-04-01 14:12:15
Subject: Re: Can Postgres 8.x start if some disks containing tablespaces are not mounted?

pgsql-jdbc by date

Next:From: Dave CramerDate: 2008-04-01 14:26:52
Subject: Re: [HACKERS] How embarrassing: optimization of a one-shot query doesn't work
Previous:From: Tom LaneDate: 2008-04-01 14:06:01
Subject: Re: [HACKERS] How embarrassing: optimization of a one-shot query doesn't work

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