Обсуждение: Re: [PERFORM] join over 12 tables takes 3 secs to plan

I have been asking (and learning) about this same thing on the
PGSQL-JDBC mailing list.  Apparently, there is a new driver for 7.3 that
can store the plan on the server (aka, preparing it on the server) and
re-use it.  However, you need to set the PreparedStatement to do this
for each statement.  So, yes, you can retain the plan but it looks like
you need to do some work to make it stick.  [Also, you need to retain
the PreparedStatement, it is not cached based based on the text of the
statement, but associated with the PreparedStatement itself].

I think the functionality is starting to become real, but it looks like
it is starting with some limitations that might restricts its use from
be maximally realized until 7.4 (or beyond).

Charlie




Hilmar Lapp wrote:

>
> On Friday, January 3, 2003, at 09:12  AM, Jeff wrote:
>
>> Hmm.. This won't fix the fact the planner takes three seconds, but since
>> it is a web application have you tried using PREPARE/EXECUTE so it only
>> needs to be planned once?
>
>
> Interesting point. I'd have to look into the source code whether the
> guy who wrote it actually uses JDBC PreparedStatements. I understand
> that PostgreSQL from 7.3 onwards supports prepared statements (cool!).
> Would the JDBC driver accompanying the dist. exploit that feature for
> its PreparedStatement implementation?
>
>     -hilmar


--


Charles H. Woloszynski

ClearMetrix, Inc.
115 Research Drive
Bethlehem, PA 18015

tel: 610-419-2210 x400
fax: 240-371-3256
web: www.clearmetrix.com

Neil:

Thanks for the feedback.  I've attached my original text to this note 
and re-posted it back to pgsql-jdbc to make sure that they are aware of 
them.  I look forward to this new and improved server-side preparation.

Charlie

Neil Conway wrote:

>On Sun, 2003-01-12 at 10:52, Charles H. Woloszynski wrote:
>  
>
>>As I understand it, the functions I am waiting for are targeted into 7.4 
>>(but I'd love to see them early and do some testing of those for the 
>>community).
>>    
>>
>
>Ok -- those are pretty much all features on the JDBC side of things (not
>the backend implementation of PREPARE/EXECUTE). I'm not sure how much of
>that is planned for 7.4: if you haven't talked to the JDBC guys about
>it, they may not be aware of your comments.
>
>Cheers,
>
>Neil
>  
>
Charles H. Woloszynski wrote:

> Neil:
>
> I think that general use of this feature should be enabled using the 
> URL, not with an API call.  We use a JDBC connection pool and it will 
> help tremendously to have the pool set to user server-side preparing 
> without having to downcast the connection to a PG connection (which I 
> think is an issue because of the facade in our connection pool code). 
> The second item is that of compatibility.  If the new code cannot 
> handle all statements (eg. something with a semi in it) and disable 
> the generation of a 'prepare' then we cannot count on the URL 
> functionality. As I understand it, the programmer is required 
> currently to enable/disable the server-side functionality by hand and 
> only when the statement to be prepared is not composite (statement1; 
> statement2; statement2).
>
> But in our real-world application space, we use a connection pool with 
> a facade, so getting to the actual connection to enable this is 
> problematic (and forces postgresql-specific coding into our framework 
> where it is not particularly welcome).  If we overcame this issue, we 
> would then need to hand-manage the enable/disable to only be used when 
> the statement is appropriately formulated (e.g., no semicolons in the 
> statement).
>
> If we could get URL enabling and auto-detection of statements that 
> won't work (and hence disable the enabled function for these 
> functions), I think we have a solution that can be deployed into 
> 'generic' app server environments with just configuration changes.  
> That is, an operations person could enable this feature and monitor 
> its impact on performance to see if/how it helps.  That is a BIG win 
> (at least to me) and a HUGE marketing item.  I'd love to test MySQL 
> with some joins over JDBC with PostgreSQL with some joins using 
> prepared statements and be able to demonstrate the big improvement 
> that this makes.
>
> As I understand it, the functions I am waiting for are targeted into 
> 7.4 (but I'd love to see them early and do some testing of those for 
> the community).




-- 


Charles H. Woloszynski

ClearMetrix, Inc.
115 Research Drive
Bethlehem, PA 18015

tel: 610-419-2210 x400
fax: 240-371-3256
web: www.clearmetrix.com