pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Charles H. Woloszynski <chw@clearmetrix.com>
To: Neil Conway <neilc@samurai.com>
Cc: Hilmar Lapp <hlapp@gmx.net>
Cc: Jeff <threshar@torgo.978.org>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: join over 12 tables takes 3 secs to plan
Date: Sun, 12 Jan 2003 10:52:11 -0500
Message-ID: <3E218F2B.8030107@clearmetrix.com> (raw)
In-Reply-To: <1042333405.386.49.camel@tokyo>
References: <FC24BF36-1F52-11D7-9CCF-000393B4BFF6@gmx.net>
	<3E160BC0.3020900@clearmetrix.com>
	<1042333405.386.49.camel@tokyo>

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).

Charlie


Neil Conway wrote:

>On Fri, 2003-01-03 at 17:16, Charles H. Woloszynski wrote:
>  
>
>>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).
>>    
>>
>
>Specifically, which limitations in this feature would you like to see
>corrected?
>
>Cheers,
>
>Neil
>  
>

-- 


Charles H. Woloszynski

ClearMetrix, Inc.
115 Research Drive
Bethlehem, PA 18015

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








view thread (22+ messages)  latest in thread

Message-ID: <3E218F2B.8030107@clearmetrix.com>
Permalink:  ../3E218F2B.8030107@clearmetrix.com/
Also on:    postgresql.org/message-id/3E218F2B.8030107@clearmetrix.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: chw@clearmetrix.com, neilc@samurai.com, hlapp@gmx.net, threshar@torgo.978.org
  Subject: Re: join over 12 tables takes 3 secs to plan
  In-Reply-To: <3E218F2B.8030107@clearmetrix.com>
  References: <FC24BF36-1F52-11D7-9CCF-000393B4BFF6@gmx.net> <3E160BC0.3020900@clearmetrix.com> <1042333405.386.49.camel@tokyo> <3E218F2B.8030107@clearmetrix.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox