Re: getImportedKeys

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(at)postgresql(dot)org
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> --
> Dave Cramer <Dave(at)micro-automation(dot)net>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>