Thread: getImportedKeys
Hi, I have a problem executing getImportedKeys. Sometimes it takes minutes to execute, sometimes it only needs a few millisconds ( with the same parameters !!!). Does anyony have a fix or workaround for this problem? Regards Martin
Martin, Can you send us a test case? Dave On Fri, 2003-01-10 at 05:02, Martin Keller wrote: > Hi, > > I have a problem executing getImportedKeys. Sometimes it takes minutes to > execute, sometimes it only needs a few millisconds ( with the same parameters > !!!). Does anyony have a fix or workaround for this problem? > > Regards > Martin > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Dave Cramer <Dave@micro-automation.net>
This isn't JDBC problem.This query work in plsql strange - one is quick, then slow ... You must >200 rows in pg_constraint for test regards Haris PecoOn Friday 10 January 2003 10:24, Dave Cramer wrote: > Martin, > > Can you send us a test case? > > Dave > > On Fri, 2003-01-10 at 05:02, Martin Keller wrote: > > Hi, > > > > I have a problem executing getImportedKeys. Sometimes it takes minutes > > to execute, sometimes it only needs a few millisconds ( with the same > > parameters !!!). Does anyony have a fix or workaround for this problem? > > > > Regards > > Martin > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
This is useful information! Just off the top of my head, postgres uses a genetic algorithm to evaluate the query plan, it is only invoked after the query reaches a certain complexity. I wonder if this is the problem? Also thinking out loud, this may be a perfect place for a server side prepared statement, Kris ? Dave On Fri, 2003-01-10 at 07:32, snpe wrote: > This isn't JDBC problem.This query work in plsql strange - one is quick, then > slow ... > You must >200 rows in pg_constraint for test > > regards > Haris PecoOn Friday 10 January 2003 10:24, Dave Cramer wrote: > > Martin, > > > > Can you send us a test case? > > > > Dave > > > > On Fri, 2003-01-10 at 05:02, Martin Keller wrote: > > > Hi, > > > > > > I have a problem executing getImportedKeys. Sometimes it takes minutes > > > to execute, sometimes it only needs a few millisconds ( with the same > > > parameters !!!). Does anyony have a fix or workaround for this problem? > > > > > > Regards > > > Martin > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Dave Cramer <Dave@micro-automation.net>
I don't know what is with this query I try with 240 rows in pg_constraint and query time is almost always diferent Sometime isn't execute (return failure for write - I have 300 mb free on disk) I send message in hackers forum, but no answer Prepared statement don't solution, I think that problem is lock referential integrity table in PostgreSQL regards Haris Peco On Friday 10 January 2003 11:35, Dave Cramer wrote: > This is useful information! Just off the top of my head, postgres uses a > genetic algorithm to evaluate the query plan, it is only invoked after > the query reaches a certain complexity. I wonder if this is the problem? > > Also thinking out loud, this may be a perfect place for a server side > prepared statement, Kris ? > > Dave > > On Fri, 2003-01-10 at 07:32, snpe wrote: > > This isn't JDBC problem.This query work in plsql strange - one is quick, > > then slow ... > > You must >200 rows in pg_constraint for test > > > > regards > > > > Haris PecoOn Friday 10 January 2003 10:24, Dave Cramer wrote: > > > Martin, > > > > > > Can you send us a test case? > > > > > > Dave > > > > > > On Fri, 2003-01-10 at 05:02, Martin Keller wrote: > > > > Hi, > > > > > > > > I have a problem executing getImportedKeys. Sometimes it takes > > > > minutes to execute, sometimes it only needs a few millisconds ( with > > > > the same parameters !!!). Does anyony have a fix or workaround for > > > > this problem? > > > > > > > > Regards > > > > Martin > > > > > > > > ---------------------------(end of > > > > broadcast)--------------------------- TIP 1: subscribe and > > > > unsubscribe commands go to majordomo@postgresql.org > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org
On 10 Jan 2003, Dave Cramer wrote: > This is useful information! Just off the top of my head, postgres uses a > genetic algorithm to evaluate the query plan, it is only invoked after > the query reaches a certain complexity. I wonder if this is the problem? Yes, there is a GUC parameter, GEQO_THRESHOLD, which determines how many elements in a from clause turns on the genetic query optimizer. With the addition of schemas to 7.3 this increased the number of tables in the getImportedKeys, getExportedKeys, and getCrossReference methods to be over the default GEQO_THRESHOLD (11). The genetic query optimizer does not produce consistent results. In my original testing I got times between 9 and 845941 msec for the same query! See my original post on this for more information. http://archives.postgresql.org/pgsql-hackers/2002-09/msg01196.php I have promised on numerous occasions to fix this by specifying an explicit join order or disabling the genetic optimizer, but this time I mean it. I'll send a patch this weekend. > Also thinking out loud, this may be a perfect place for a server side > prepared statement, Kris ? I'm not so sure about that. Won't a prepared query go through the same planning phase triggering the GEQO optimizer? The query planning does not take long, the execution of the bad plan takes a long time. Personally I'm not all that excited about prepared queries. I'm a little suspicious of how good a plan the optimizer can generate without the values of the query parameters, especially in the case of wildly different selectivities for two values for the same parameter. Kris Jurka > Dave > > On Fri, 2003-01-10 at 07:32, snpe wrote: > > This isn't JDBC problem.This query work in plsql strange - one is quick, then > > slow ... > > You must >200 rows in pg_constraint for test > > > > regards > > Haris PecoOn Friday 10 January 2003 10:24, Dave Cramer wrote: > > > Martin, > > > > > > Can you send us a test case? > > > > > > Dave > > > > > > On Fri, 2003-01-10 at 05:02, Martin Keller wrote: > > > > Hi, > > > > > > > > I have a problem executing getImportedKeys. Sometimes it takes minutes > > > > to execute, sometimes it only needs a few millisconds ( with the same > > > > parameters !!!). Does anyony have a fix or workaround for this problem? > > > > > > > > Regards > > > > Martin > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > -- > Dave Cramer <Dave@micro-automation.net> > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >