Thread: getImportedKeys

getImportedKeys

From
Martin Keller
Date:
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

Re: getImportedKeys

From
Dave Cramer
Date:
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>


Re: getImportedKeys

From
snpe
Date:
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


Re: getImportedKeys

From
Dave Cramer
Date:
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>


Re: getImportedKeys

From
snpe
Date:
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


Re: getImportedKeys

From
Kris Jurka
Date:

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