pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
join over 12 tables takes 3 secs to plan
22+ messages / 8 participants
[nested] [flat]

* join over 12 tables takes 3 secs to plan
@ 2003-01-02 19:42 Hilmar Lapp <hlapp@gmx.net>
  2003-01-02 20:24 ` Re: join over 12 tables takes 3 secs to plan Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-02 20:41 ` Re: join over 12 tables takes 3 secs to plan Neil Conway <neilc@samurai.com>
  2003-01-03 17:12 ` Re: join over 12 tables takes 3 secs to plan Jeff <threshar@torgo.978.org>
  0 siblings, 3 replies; 22+ messages in thread

From: Hilmar Lapp @ 2003-01-02 19:42 UTC (permalink / raw)
  To: pgsql-performance

I have a query generated by an application (not mine, but there's 
nothing I can find that looks bad about the query itself) that takes an 
excessive amount of time to return even though there are almost no rows 
in the schema yet. 3 secs may not seem to be much, but the query is run 
by a web-application for a page you have to go through quite 
frequently, and it appears the query should be able to execute below 1 
sec easily. I'm running Postgres 7.3.1 on Mac OSX.

After having turned on several logging options, here is a pertinent 
excerpt from the log that also shows the query. It seems the query 
planner takes the whole time, not the actual execution. Does anyone 
have an idea what's going on here, and what I could do to alleviate the 
problem? (Just to mention, I've run the same with GEQO off and if 
anything it makes the timing worse.)

2003-01-02 11:22:59 LOG:  query: SELECT TW.WORKITEMKEY, 
TW.PACKAGESYNOPSYS, TW.PACKAGEDESCRIPTION, TW.BUILD,
TW.LASTEDIT, TOW.LASTNAME AS LOWNER, TOW.FIRSTNAME AS FOWNER,
TOR.LASTNAME AS LORIGINATOR, TOR.FIRSTNAME AS FORIGINATOR,
TRE.LASTNAME AS LRESPONSIBLE, TRE.FIRSTNAME AS FRESPONSIBLE,
TPRJC.LABEL AS PROJCATLABEL, TPRJ.LABEL AS PROJLABEL, TCL.LABEL AS 
REQCLASS,
TW.CATEGORYKEY AS REQCATEGORY, TW.PRIORITYKEY AS REQPRIORITY,
TW.SEVERITYKEY AS REQSEVERITY, TST.LABEL AS STATELABEL,  TW.STATE,
TST.STATEFLAG, TREL.LABEL AS RELEASELABEL, TW.ENDDATE
FROM TWORKITEM TW, TPERSON TOW, TPERSON TOR, TPERSON TRE, TPROJECT TPRJ,
TPROJCAT TPRJC, TCATEGORY TCAT, TCLASS TCL, TPRIORITY TPRIO, TSEVERITY 
TSEV,
TSTATE TST, TRELEASE TREL
WHERE (TW.OWNER = TOW.PKEY) AND (TW.ORIGINATOR = TOR.PKEY)
AND (TW.RESPONSIBLE = TRE.PKEY) AND (TW.PROJCATKEY = TPRJC.PKEY)
AND (TPRJ.PKEY = TPRJC.PROJKEY) AND (TW.CLASSKEY = TCL.PKEY)
AND (TW.CATEGORYKEY = TCAT.PKEY) AND (TW.PRIORITYKEY = TPRIO.PKEY)
AND (TW.SEVERITYKEY = TSEV.PKEY) AND (TST.PKEY = TW.STATE)
AND (TREL.PKEY = TW.RELSCHEDULEDKEY)

2003-01-02 11:23:02 LOG:  PLANNER STATISTICS
! system usage stats:
!       2.730501 elapsed 1.400000 user 0.000000 system sec
!       [3.580000 user 0.000000 sys total]
!       0/0 [0/0] filesystem blocks in/out
!       0/0 [0/0] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/14] messages rcvd/sent
!       0/0 [24/0] voluntary/involuntary context switches
! buffer usage stats:
!       Shared blocks:          0 read,          0 written, buffer hit 
rate = 0.00%
!       Local  blocks:          0 read,          0 written, buffer hit 
rate = 0.00%
!       Direct blocks:          0 read,          0 written
2003-01-02 11:23:02 LOG:  EXECUTOR STATISTICS
! system usage stats:
!       0.005024 elapsed 0.000000 user 0.000000 system sec
!       [3.580000 user 0.000000 sys total]
!       0/0 [0/0] filesystem blocks in/out
!       0/0 [0/0] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/14] messages rcvd/sent
!       0/0 [24/0] voluntary/involuntary context switches
! buffer usage stats:
!       Shared blocks:          0 read,          0 written, buffer hit 
rate = 100.00%
!       Local  blocks:          0 read,          0 written, buffer hit 
rate = 0.00%
!       Direct blocks:          0 read,          0 written
2003-01-02 11:23:02 LOG:  duration: 2.740243 sec
2003-01-02 11:23:02 LOG:  QUERY STATISTICS
! system usage stats:
!       0.006432 elapsed 0.000000 user 0.000000 system sec
!       [3.580000 user 0.000000 sys total]
!       0/0 [0/0] filesystem blocks in/out
!       0/0 [0/0] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/14] messages rcvd/sent
!       0/0 [24/0] voluntary/involuntary context switches
! buffer usage stats:
!       Shared blocks:          0 read,          0 written, buffer hit 
rate = 100.00%
!       Local  blocks:          0 read,          0 written, buffer hit 
rate = 0.00%
!       Direct blocks:          0 read,          0 written

-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: join over 12 tables takes 3 secs to plan
  2003-01-02 19:42 join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
@ 2003-01-02 20:24 ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-02 21:03   ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2 siblings, 1 reply; 22+ messages in thread

From: Tom Lane @ 2003-01-02 20:24 UTC (permalink / raw)
  To: Hilmar Lapp <hlapp@gmx.net>; +Cc: pgsql-performance

Hilmar Lapp <hlapp@gmx.net> writes:
> I have a query generated by an application (not mine, but there's 
> nothing I can find that looks bad about the query itself) that takes an 
> excessive amount of time to return even though there are almost no rows 
> in the schema yet.

Read
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/explicit-joins.html

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: join over 12 tables takes 3 secs to plan
  2003-01-02 19:42 join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-02 20:24 ` Re: join over 12 tables takes 3 secs to plan Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-01-02 21:03   ` Hilmar Lapp <hlapp@gmx.net>
  2003-01-02 21:21     ` Re: join over 12 tables takes 3 secs to plan Neil Conway <neilc@samurai.com>
  2003-01-02 21:40     ` Re: join over 12 tables takes 3 secs to plan Joe Conway <mail@joeconway.com>
  0 siblings, 2 replies; 22+ messages in thread

From: Hilmar Lapp @ 2003-01-02 21:03 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Thanks for the pointer Tom. The application that's generating those  
queries is open source, so I could even go in and hack the query  
generating code accordingly, but I doubt I can spare that time. Given  
the information in the document you pointed me at and Neil's email I  
assume there is no other immediate remedy.

As an added note, appreciating that query optimization is a difficult  
problem, and I do think PostgreSQL is a great product. Having said  
that, I've written 16-table joins for Oracle and always found them to  
plan within a second or two, so that's why I thought there's nothing  
special about the query I posted ... I'm not saying this to be bashful  
about PostgreSQL, but rather to suggest that apparently there are ways  
to do it pretty fast.

I'm only starting to use PostgreSQL and making experiences, so I'm  
asking for forgiveness what may occasionally seem to be ignorant ...

	-hilmar

On Thursday, January 2, 2003, at 12:24  PM, Tom Lane wrote:

> Hilmar Lapp <hlapp@gmx.net> writes:
>> I have a query generated by an application (not mine, but there's
>> nothing I can find that looks bad about the query itself) that takes  
>> an
>> excessive amount of time to return even though there are almost no  
>> rows
>> in the schema yet.
>
> Read
> http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/explicit- 
> joins.html
>
> 			regards, tom lane
>
>
-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: join over 12 tables takes 3 secs to plan
  2003-01-02 19:42 join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-02 20:24 ` Re: join over 12 tables takes 3 secs to plan Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-02 21:03   ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
@ 2003-01-02 21:21     ` Neil Conway <neilc@samurai.com>
  2003-01-02 21:29       ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  1 sibling, 1 reply; 22+ messages in thread

From: Neil Conway @ 2003-01-02 21:21 UTC (permalink / raw)
  To: hlapp@gmx.net; +Cc: tgl@sss.pgh.pa.us; pgsql-performance

Hilmar Lapp said:
> As an added note, appreciating that query optimization is a difficult
> problem, and I do think PostgreSQL is a great product. Having said
> that, I've written 16-table joins for Oracle and always found them to
> plan within a second or two, so that's why I thought there's nothing
> special about the query I posted ... I'm not saying this to be bashful
> about PostgreSQL, but rather to suggest that apparently there are ways
> to do it pretty fast.

I'm sure there is room for improvement -- either by adding additional
heuristics to the default optimizer, by improving GEQO, or by implementing
another method for non-exhaustive search for large join queries (there are
several ways to handle large join queries, only one of which uses a
genetic algorithm: see "Query Optimization" (Ioannidis, 1996) for a good
introductory survey).

If you'd like to take a shot at improving it, let me know if I can be of
any assistance :-)

Cheers,

Neil





^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: join over 12 tables takes 3 secs to plan
  2003-01-02 19:42 join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-02 20:24 ` Re: join over 12 tables takes 3 secs to plan Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-02 21:03   ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-02 21:21     ` Re: join over 12 tables takes 3 secs to plan Neil Conway <neilc@samurai.com>
@ 2003-01-02 21:29       ` Hilmar Lapp <hlapp@gmx.net>
  0 siblings, 0 replies; 22+ messages in thread

From: Hilmar Lapp @ 2003-01-02 21:29 UTC (permalink / raw)
  To: Neil Conway <neilc@samurai.com>; +Cc: tgl@sss.pgh.pa.us; pgsql-performance


On Thursday, January 2, 2003, at 01:21  PM, Neil Conway wrote:

> If you'd like to take a shot at improving it, let me know if I can be 
> of
> any assistance :-)
>
>

Would be a very cool problem to work on once I enroll in a CS program 
:-)

	-hilmar

-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: join over 12 tables takes 3 secs to plan
  2003-01-02 19:42 join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-02 20:24 ` Re: join over 12 tables takes 3 secs to plan Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-02 21:03   ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
@ 2003-01-02 21:40     ` Joe Conway <mail@joeconway.com>
  2003-01-02 22:49       ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-03 00:01       ` Re: join over 12 tables takes 3 secs to plan Ron Johnson <ron.l.johnson@cox.net>
  1 sibling, 2 replies; 22+ messages in thread

From: Joe Conway @ 2003-01-02 21:40 UTC (permalink / raw)
  To: Hilmar Lapp <hlapp@gmx.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance

Hilmar Lapp wrote:
> As an added note, appreciating that query optimization is a difficult  
> problem, and I do think PostgreSQL is a great product. Having said  
> that, I've written 16-table joins for Oracle and always found them to  
> plan within a second or two, so that's why I thought there's nothing  
> special about the query I posted ... I'm not saying this to be bashful  
> about PostgreSQL, but rather to suggest that apparently there are ways  
> to do it pretty fast.

I could be wrong, but I believe Oracle uses its rule based optimizer by 
default, not its cost based optimizer. A rule based optimizer will be very 
quick all the time, but might not pick the best plan all the time, because it 
doesn't consider the statistics of the data. Any idea which one you were using 
in your Oracle experience?

Joe




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: join over 12 tables takes 3 secs to plan
  2003-01-02 19:42 join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-02 20:24 ` Re: join over 12 tables takes 3 secs to plan Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-02 21:03   ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-02 21:40     ` Re: join over 12 tables takes 3 secs to plan Joe Conway <mail@joeconway.com>
@ 2003-01-02 22:49       ` Hilmar Lapp <hlapp@gmx.net>
  1 sibling, 0 replies; 22+ messages in thread

From: Hilmar Lapp @ 2003-01-02 22:49 UTC (permalink / raw)
  To: Joe Conway <mail@joeconway.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance


On Thursday, January 2, 2003, at 01:40  PM, Joe Conway wrote:

> I could be wrong, but I believe Oracle uses its rule based optimizer 
> by default, not its cost based optimizer.

They changed it from 9i on. The cost-based is now the default. The 
recent 16-table join example I was referring to was on the cost-based 
optimizer.

They actually did an amazing good job on the CBO, at least in my 
experience. I caught it screwing up badly only once, only to realize 
that I had forgotten to compute the statistics ... It also allows for 
different plans depending on whether you want some rows fast and the 
total not necessarily as fast, or all rows as fast as possible. This 
also caught me off-guard initially when I wanted to peek into the first 
rows returned and had to wait almost as long as the entire query to 
return. (optimizing for all rows is the default)

>  A rule based optimizer will be very quick all the time, but might not 
> pick the best plan all the time, because it doesn't consider the 
> statistics of the data.

True. In a situation with not that many rows though even a sub-optimal 
plan that takes 10x longer to execute than the possibly best (e.g., 1s 
vs 0.1s), but plans 10x faster (e.g. 0.3s vs 3s), might still return 
significantly sooner. Especially if some of the tables have been cached 
in memory already ...

	-hilmar
-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: join over 12 tables takes 3 secs to plan
  2003-01-02 19:42 join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-02 20:24 ` Re: join over 12 tables takes 3 secs to plan Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-02 21:03   ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-02 21:40     ` Re: join over 12 tables takes 3 secs to plan Joe Conway <mail@joeconway.com>
@ 2003-01-03 00:01       ` Ron Johnson <ron.l.johnson@cox.net>
  2003-01-03 00:24         ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  1 sibling, 1 reply; 22+ messages in thread

From: Ron Johnson @ 2003-01-03 00:01 UTC (permalink / raw)
  To: pgsql-performance

On Thu, 2003-01-02 at 15:40, Joe Conway wrote:
> Hilmar Lapp wrote:
> > As an added note, appreciating that query optimization is a difficult  
> > problem, and I do think PostgreSQL is a great product. Having said  
> > that, I've written 16-table joins for Oracle and always found them to  
> > plan within a second or two, so that's why I thought there's nothing  
> > special about the query I posted ... I'm not saying this to be bashful  
> > about PostgreSQL, but rather to suggest that apparently there are ways  
> > to do it pretty fast.
> 
> I could be wrong, but I believe Oracle uses its rule based optimizer by 
> default, not its cost based optimizer. A rule based optimizer will be very 
> quick all the time, but might not pick the best plan all the time, because it 
> doesn't consider the statistics of the data. Any idea which one you were using 
> in your Oracle experience?

Remember also that the commercial RDMBSs have had many engineers working
for many years on these problems, whereas PostgreSQL hasn't...

Could it be that PG isn't the proper tool for the job?  Of course,
at USD20K/cp, Oracle may be slightly out of budget.

-- 
+------------------------------------------------------------+
| Ron Johnson, Jr.     mailto:ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson  |
|                                                            |
| "Basically, I got on the plane with a bomb. Basically, I   |
|  tried to ignite it. Basically, yeah, I intended to damage |
|  the plane."                                               |
|    RICHARD REID, who tried to blow up American Airlines    |
|                  Flight 63                                 |
+------------------------------------------------------------+




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: join over 12 tables takes 3 secs to plan
  2003-01-02 19:42 join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-02 20:24 ` Re: join over 12 tables takes 3 secs to plan Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-02 21:03   ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-02 21:40     ` Re: join over 12 tables takes 3 secs to plan Joe Conway <mail@joeconway.com>
  2003-01-03 00:01       ` Re: join over 12 tables takes 3 secs to plan Ron Johnson <ron.l.johnson@cox.net>
@ 2003-01-03 00:24         ` Hilmar Lapp <hlapp@gmx.net>
  2003-01-03 00:36           ` Re: join over 12 tables takes 3 secs to plan Charles H. Woloszynski <chw@clearmetrix.com>
  2003-01-03 17:01           ` Re: join over 12 tables takes 3 secs to plan scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 2 replies; 22+ messages in thread

From: Hilmar Lapp @ 2003-01-03 00:24 UTC (permalink / raw)
  To: Ron Johnson <ron.l.johnson@cox.net>; +Cc: pgsql-performance


On Thursday, January 2, 2003, at 04:01  PM, Ron Johnson wrote:

>
> Could it be that PG isn't the proper tool for the job?  Of course,
> at USD20K/cp, Oracle may be slightly out of budget.
>
>

We are in fact an Oracle shop, but the application I tried to get 
running (http://trackplus.sourceforge.net/) I wanted to run on an OSS 
RDBMS so that I could easily move it onto my laptop etc (BTW apparently 
it was primarily developed on InterBase/Firebird). Anyway, I was able 
to cut the planning time for those queries in half by setting 
geqo_pool_size to 512. However, now it gets stuck for an excessive 
amount of time after the issue update page and I have no idea what's 
going on, and I'm not in the mood to track it down. So finally I'm 
giving up and I'm rolling it out on MySQL on which it is working fine, 
even though I don't like MySQL to say the least.

	-hilmar

-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: join over 12 tables takes 3 secs to plan
  2003-01-02 19:42 join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-02 20:24 ` Re: join over 12 tables takes 3 secs to plan Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-02 21:03   ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-02 21:40     ` Re: join over 12 tables takes 3 secs to plan Joe Conway <mail@joeconway.com>
  2003-01-03 00:01       ` Re: join over 12 tables takes 3 secs to plan Ron Johnson <ron.l.johnson@cox.net>
  2003-01-03 00:24         ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
@ 2003-01-03 00:36           ` Charles H. Woloszynski <chw@clearmetrix.com>
  1 sibling, 0 replies; 22+ messages in thread

From: Charles H. Woloszynski @ 2003-01-03 00:36 UTC (permalink / raw)
  To: Hilmar Lapp <hlapp@gmx.net>; +Cc: Ron Johnson <ron.l.johnson@cox.net>; pgsql-performance



Hilmar Lapp wrote:

> We are in fact an Oracle shop, but the application I tried to get 
> running (http://trackplus.sourceforge.net/) I wanted to run on an OSS 
> RDBMS so that I could easily move it onto my laptop etc (BTW 
> apparently it was primarily developed on InterBase/Firebird). Anyway, 
> I was able to cut the planning time for those queries in half by 
> setting geqo_pool_size to 512. However, now it gets stuck for an 
> excessive amount of time after the issue update page and I have no 
> idea what's going on, and I'm not in the mood to track it down. So 
> finally I'm giving up and I'm rolling it out on MySQL on which it is 
> working fine, even though I don't like MySQL to say the least.
>
>     -hilmar
>
Uhoh, did I just hear a gauntlet thrown down ... works well on MySQL but 
not on PostgreSQL.  If I can find the time, perhaps I can take a look at 
the specific query(ies) and see what is missed in PostgreSQL that MySQL 
has gotten right.

If only there were 48 hours in a day :-).

Charlie

-- 


Charles H. Woloszynski

ClearMetrix, Inc.
115 Research Drive
Bethlehem, PA 18015

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








^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: join over 12 tables takes 3 secs to plan
  2003-01-02 19:42 join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-02 20:24 ` Re: join over 12 tables takes 3 secs to plan Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-02 21:03   ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-02 21:40     ` Re: join over 12 tables takes 3 secs to plan Joe Conway <mail@joeconway.com>
  2003-01-03 00:01       ` Re: join over 12 tables takes 3 secs to plan Ron Johnson <ron.l.johnson@cox.net>
  2003-01-03 00:24         ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
@ 2003-01-03 17:01           ` scott.marlowe <scott.marlowe@ihs.com>
  2003-01-03 22:09             ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  1 sibling, 1 reply; 22+ messages in thread

From: scott.marlowe @ 2003-01-03 17:01 UTC (permalink / raw)
  To: Hilmar Lapp <hlapp@gmx.net>; +Cc: Ron Johnson <ron.l.johnson@COX.NET>; pgsql-performance

On Thu, 2 Jan 2003, Hilmar Lapp wrote:

> 
> On Thursday, January 2, 2003, at 04:01  PM, Ron Johnson wrote:
> 
> >
> > Could it be that PG isn't the proper tool for the job?  Of course,
> > at USD20K/cp, Oracle may be slightly out of budget.
> >
> >
> 
> We are in fact an Oracle shop, but the application I tried to get 
> running (http://trackplus.sourceforge.net/) I wanted to run on an OSS 
> RDBMS so that I could easily move it onto my laptop etc (BTW apparently 
> it was primarily developed on InterBase/Firebird). Anyway, I was able 
> to cut the planning time for those queries in half by setting 
> geqo_pool_size to 512. However, now it gets stuck for an excessive 
> amount of time after the issue update page and I have no idea what's 
> going on, and I'm not in the mood to track it down. So finally I'm 
> giving up and I'm rolling it out on MySQL on which it is working fine, 
> even though I don't like MySQL to say the least.

Have you tried it on firebird for linux?  It's an actively developed rdbms 
that's open source too.  If this was developed for it, it might be a 
better fit to use that for now, and then learn postgresql under the less 
rigorous schedule of simply porting, not having to get a product out the 
door.

Is an explicit join the answer here?  i.e. will the number of rows we get 
from each table in a single query likely to never change?  If so then you 
could just make an explicit join and be done with it.




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: join over 12 tables takes 3 secs to plan
  2003-01-02 19:42 join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-02 20:24 ` Re: join over 12 tables takes 3 secs to plan Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-02 21:03   ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-02 21:40     ` Re: join over 12 tables takes 3 secs to plan Joe Conway <mail@joeconway.com>
  2003-01-03 00:01       ` Re: join over 12 tables takes 3 secs to plan Ron Johnson <ron.l.johnson@cox.net>
  2003-01-03 00:24         ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-03 17:01           ` Re: join over 12 tables takes 3 secs to plan scott.marlowe <scott.marlowe@ihs.com>
@ 2003-01-03 22:09             ` Hilmar Lapp <hlapp@gmx.net>
  0 siblings, 0 replies; 22+ messages in thread

From: Hilmar Lapp @ 2003-01-03 22:09 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Ron Johnson <ron.l.johnson@COX.NET>; pgsql-performance


On Friday, January 3, 2003, at 09:01  AM, scott.marlowe wrote:

>
> Have you tried it on firebird for linux?  It's an actively developed 
> rdbms
> that's open source too.  If this was developed for it, it might be a
> better fit to use that for now,

Probably it would. But honestly I'm not that keen to install the 3rd 
OSS database (in addition to Oracle, MySQL, PostgreSQL), and my 
sysadmins probably wouldn't be cheerfully jumping either ...


>  and then learn postgresql under the less
> rigorous schedule of simply porting, not having to get a product out 
> the
> door.

Yes, so odd MySQL fit that bill for now ...

>
> Is an explicit join the answer here?  i.e. will the number of rows we 
> get
> from each table in a single query likely to never change?  If so then 
> you
> could just make an explicit join and be done with it.
>

Probably, even though the number of rows will change over time, but not 
by magnitudes. It's not an application of ours though, and since we're 
a bioinformatics shop, I'm not that eager to spend time hacking a 
project management system's query generation code.

Thanks for all the thoughts and comments from you and others though, I 
appreciate that.

	-hilmar
-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: join over 12 tables takes 3 secs to plan
  2003-01-02 19:42 join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
@ 2003-01-02 20:41 ` Neil Conway <neilc@samurai.com>
  2003-01-02 21:08   ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2 siblings, 1 reply; 22+ messages in thread

From: Neil Conway @ 2003-01-02 20:41 UTC (permalink / raw)
  To: hlapp@gmx.net; +Cc: pgsql-performance

Hilmar Lapp said:
> I have a query generated by an application (not mine, but there's
> nothing I can find that looks bad about the query itself) that takes an
> excessive amount of time to return even though there are almost no rows
> in the schema yet.

Yes -- an exhaustive search to determine the correct join order for a
multiple relation query is similar to solving the traveling salesman
problem (only more difficult, due to the availability of different join
algorithms, etc.). GEQO should be faster than the default optimizer for
large queries involving large numbers of joins, but it's still going to
take a fair bit of time.

In other words, it's not a surprise that a 12-relation join takes a little
while to plan.

> I'm running Postgres 7.3.1 on Mac OSX.

Tom recently checked in some optimizations for GEQO in CVS HEAD, so you
could try using that (or at least testing it, so you have an idea of what
7.4 will perform like).

You could also try using prepared queries.

Finally, there are a bunch of GEQO tuning parameters that you might want
to play with. They should allow you to reduce the planning time a bit, in
exchange for possibly generating an inferior plan.

Cheers,

Neil





^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: join over 12 tables takes 3 secs to plan
  2003-01-02 19:42 join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-02 20:41 ` Re: join over 12 tables takes 3 secs to plan Neil Conway <neilc@samurai.com>
@ 2003-01-02 21:08   ` Hilmar Lapp <hlapp@gmx.net>
  2003-01-02 21:11     ` Re: join over 12 tables takes 3 secs to plan Neil Conway <neilc@samurai.com>
  0 siblings, 1 reply; 22+ messages in thread

From: Hilmar Lapp @ 2003-01-02 21:08 UTC (permalink / raw)
  To: Neil Conway <neilc@samurai.com>; +Cc: pgsql-performance


On Thursday, January 2, 2003, at 12:41  PM, Neil Conway wrote:

>
> Finally, there are a bunch of GEQO tuning parameters that you might 
> want
> to play with. They should allow you to reduce the planning time a bit, 
> in
> exchange for possibly generating an inferior plan.
>
>

Thanks for the tip. I have to admit that I have zero experience with 
tuning GAs. If anyone could provide a starter which parameters are best 
to start with? Or is it in the docs?

	-hilmar

-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: join over 12 tables takes 3 secs to plan
  2003-01-02 19:42 join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-02 20:41 ` Re: join over 12 tables takes 3 secs to plan Neil Conway <neilc@samurai.com>
  2003-01-02 21:08   ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
@ 2003-01-02 21:11     ` Neil Conway <neilc@samurai.com>
  0 siblings, 0 replies; 22+ messages in thread

From: Neil Conway @ 2003-01-02 21:11 UTC (permalink / raw)
  To: hlapp@gmx.net; +Cc: pgsql-performance

Hilmar Lapp said:
> Thanks for the tip. I have to admit that I have zero experience with
> tuning GAs. If anyone could provide a starter which parameters are best
> to start with? Or is it in the docs?

http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/runtime-config.html
lists the available options.

I'd think that GEQO_EFFORT, GEQO_GENERATIONS, and GEQO_POOL_SIZE would be
the parameters that would effect performance the most.

Cheers,

Neil





^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: join over 12 tables takes 3 secs to plan
  2003-01-02 19:42 join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
@ 2003-01-03 17:12 ` Jeff <threshar@torgo.978.org>
  2003-01-03 19:38   ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2 siblings, 1 reply; 22+ messages in thread

From: Jeff @ 2003-01-03 17:12 UTC (permalink / raw)
  To: Hilmar Lapp <hlapp@gmx.net>; +Cc: pgsql-performance

On Thu, 2 Jan 2003, Hilmar Lapp wrote:

> I have a query generated by an application (not mine, but there's
> nothing I can find that looks bad about the query itself) that takes an
> excessive amount of time to return even though there are almost no rows
> in the schema yet. 3 secs may not seem to be much, but the query is run
> by a web-application for a page you have to go through quite
> frequently, and it appears the query should be able to execute below 1
> sec easily. I'm running Postgres 7.3.1 on Mac OSX.
>

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? (Unless I am mistaken about what prepare/execute
actually do) that way only the first visitor gets the hit..

------------------------------------------------------------------------------
Jeff Trout <jeff@jefftrout.com>                  http://www.jefftrout.com/
   Ronald McDonald, with the help of cheese soup,
       controls America from a secret volkswagon hidden in the past
-------------------------------------------------------------------------------





^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: join over 12 tables takes 3 secs to plan
  2003-01-02 19:42 join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-03 17:12 ` Re: join over 12 tables takes 3 secs to plan Jeff <threshar@torgo.978.org>
@ 2003-01-03 19:38   ` Hilmar Lapp <hlapp@gmx.net>
  2003-01-03 22:16     ` Re: [PERFORM] join over 12 tables takes 3 secs to plan Charles H. Woloszynski <chw@clearmetrix.com>
  0 siblings, 1 reply; 22+ messages in thread

From: Hilmar Lapp @ 2003-01-03 19:38 UTC (permalink / raw)
  To: Jeff <threshar@torgo.978.org>; +Cc: pgsql-performance


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
-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: [PERFORM] join over 12 tables takes 3 secs to plan
  2003-01-02 19:42 join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-03 17:12 ` Re: join over 12 tables takes 3 secs to plan Jeff <threshar@torgo.978.org>
  2003-01-03 19:38   ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
@ 2003-01-03 22:16     ` Charles H. Woloszynski <chw@clearmetrix.com>
  2003-01-03 22:22       ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-12 01:03       ` Re: join over 12 tables takes 3 secs to plan Neil Conway <neilc@samurai.com>
  0 siblings, 2 replies; 22+ messages in thread

From: Charles H. Woloszynski @ 2003-01-03 22:16 UTC (permalink / raw)
  To: Hilmar Lapp <hlapp@gmx.net>; +Cc: Jeff <threshar@torgo.978.org>; pgsql-performance

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








^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: join over 12 tables takes 3 secs to plan
  2003-01-02 19:42 join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-03 17:12 ` Re: join over 12 tables takes 3 secs to plan Jeff <threshar@torgo.978.org>
  2003-01-03 19:38   ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-03 22:16     ` Re: [PERFORM] join over 12 tables takes 3 secs to plan Charles H. Woloszynski <chw@clearmetrix.com>
@ 2003-01-03 22:22       ` Hilmar Lapp <hlapp@gmx.net>
  1 sibling, 0 replies; 22+ messages in thread

From: Hilmar Lapp @ 2003-01-03 22:22 UTC (permalink / raw)
  To: Charles H. Woloszynski <chw@clearmetrix.com>; +Cc: Jeff <threshar@torgo.978.org>; pgsql-performance


On Friday, January 3, 2003, at 02:16  PM, Charles H. Woloszynski wrote:

> 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 that's normal. I don't recall the JDBC spec saying that you 
have a chance the server will remember that you created a 
PreparedStatement for the same query text before. You have to cache the 
PreparedStatement object in your app, not the query string.

BTW that's the same for perl/DBI. At least for Oracle.

	-hilmar
-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: join over 12 tables takes 3 secs to plan
  2003-01-02 19:42 join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-03 17:12 ` Re: join over 12 tables takes 3 secs to plan Jeff <threshar@torgo.978.org>
  2003-01-03 19:38   ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-03 22:16     ` Re: [PERFORM] join over 12 tables takes 3 secs to plan Charles H. Woloszynski <chw@clearmetrix.com>
@ 2003-01-12 01:03       ` Neil Conway <neilc@samurai.com>
  2003-01-12 15:52         ` Re: join over 12 tables takes 3 secs to plan Charles H. Woloszynski <chw@clearmetrix.com>
  1 sibling, 1 reply; 22+ messages in thread

From: Neil Conway @ 2003-01-12 01:03 UTC (permalink / raw)
  To: Charles H. Woloszynski <chw@clearmetrix.com>; +Cc: Hilmar Lapp <hlapp@gmx.net>; Jeff <threshar@torgo.978.org>; pgsql-performance

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
-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC






^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: join over 12 tables takes 3 secs to plan
  2003-01-02 19:42 join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-03 17:12 ` Re: join over 12 tables takes 3 secs to plan Jeff <threshar@torgo.978.org>
  2003-01-03 19:38   ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-03 22:16     ` Re: [PERFORM] join over 12 tables takes 3 secs to plan Charles H. Woloszynski <chw@clearmetrix.com>
  2003-01-12 01:03       ` Re: join over 12 tables takes 3 secs to plan Neil Conway <neilc@samurai.com>
@ 2003-01-12 15:52         ` Charles H. Woloszynski <chw@clearmetrix.com>
  2003-01-13 13:08           ` Re: [PERFORM] join over 12 tables takes 3 secs to plan Charles H. Woloszynski <chw@clearmetrix.com>
  0 siblings, 1 reply; 22+ messages in thread

From: Charles H. Woloszynski @ 2003-01-12 15:52 UTC (permalink / raw)
  To: Neil Conway <neilc@samurai.com>; +Cc: Hilmar Lapp <hlapp@gmx.net>; Jeff <threshar@torgo.978.org>; pgsql-performance

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








^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: [PERFORM] join over 12 tables takes 3 secs to plan
  2003-01-02 19:42 join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-03 17:12 ` Re: join over 12 tables takes 3 secs to plan Jeff <threshar@torgo.978.org>
  2003-01-03 19:38   ` Re: join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
  2003-01-03 22:16     ` Re: [PERFORM] join over 12 tables takes 3 secs to plan Charles H. Woloszynski <chw@clearmetrix.com>
  2003-01-12 01:03       ` Re: join over 12 tables takes 3 secs to plan Neil Conway <neilc@samurai.com>
  2003-01-12 15:52         ` Re: join over 12 tables takes 3 secs to plan Charles H. Woloszynski <chw@clearmetrix.com>
@ 2003-01-13 13:08           ` Charles H. Woloszynski <chw@clearmetrix.com>
  0 siblings, 0 replies; 22+ messages in thread

From: Charles H. Woloszynski @ 2003-01-13 13:08 UTC (permalink / raw)
  To: Neil Conway <neilc@samurai.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








^ permalink  raw  reply  [nested|flat] 22+ messages in thread


end of thread, other threads:[~2003-01-13 13:08 UTC | newest]

Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-02 19:42 join over 12 tables takes 3 secs to plan Hilmar Lapp <hlapp@gmx.net>
2003-01-02 20:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-02 21:03   ` Hilmar Lapp <hlapp@gmx.net>
2003-01-02 21:21     ` Neil Conway <neilc@samurai.com>
2003-01-02 21:29       ` Hilmar Lapp <hlapp@gmx.net>
2003-01-02 21:40     ` Joe Conway <mail@joeconway.com>
2003-01-02 22:49       ` Hilmar Lapp <hlapp@gmx.net>
2003-01-03 00:01       ` Ron Johnson <ron.l.johnson@cox.net>
2003-01-03 00:24         ` Hilmar Lapp <hlapp@gmx.net>
2003-01-03 00:36           ` Charles H. Woloszynski <chw@clearmetrix.com>
2003-01-03 17:01           ` scott.marlowe <scott.marlowe@ihs.com>
2003-01-03 22:09             ` Hilmar Lapp <hlapp@gmx.net>
2003-01-02 20:41 ` Neil Conway <neilc@samurai.com>
2003-01-02 21:08   ` Hilmar Lapp <hlapp@gmx.net>
2003-01-02 21:11     ` Neil Conway <neilc@samurai.com>
2003-01-03 17:12 ` Jeff <threshar@torgo.978.org>
2003-01-03 19:38   ` Hilmar Lapp <hlapp@gmx.net>
2003-01-03 22:16     ` Charles H. Woloszynski <chw@clearmetrix.com>
2003-01-03 22:22       ` Hilmar Lapp <hlapp@gmx.net>
2003-01-12 01:03       ` Neil Conway <neilc@samurai.com>
2003-01-12 15:52         ` Charles H. Woloszynski <chw@clearmetrix.com>
2003-01-13 13:08           ` Charles H. Woloszynski <chw@clearmetrix.com>

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