Thread: join over 12 tables takes 3 secs to plan
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 -------------------------------------------------------------
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 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
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
-------------------------------------------------------------
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
-------------------------------------------------------------
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
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
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
-------------------------------------------------------------
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
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
-------------------------------------------------------------
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 | +------------------------------------------------------------+
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 -------------------------------------------------------------
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
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.
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 -------------------------------------------------------------------------------
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
-------------------------------------------------------------
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
-------------------------------------------------------------
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
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
-------------------------------------------------------------
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
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