Thread: Eh?

Eh?

From
"Josh Berkus"
Date:
Tom, Stephan,

SHould I be concerned about this?

DEBUG:  geqo_main: using edge recombination crossover [ERX]
DEBUG:  geqo_main: using edge recombination crossover [ERX]

It happens when I run a truly massive (>2000 chars) query ...

-Josh


______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 


Re: Eh?

From
Tom Lane
Date:
"Josh Berkus" <josh@agliodbs.com> writes:
> SHould I be concerned about this?

> DEBUG:  geqo_main: using edge recombination crossover [ERX]

Nope.  See
http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/geqo.html
        regards, tom lane


Re: Eh?

From
"Josh Berkus"
Date:
Tom,

> > SHould I be concerned about this?
>
> > DEBUG:  geqo_main: using edge recombination crossover [ERX]
>
> Nope.  See
> http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/geqo.html

Cool!  I love it when I actually get the chance to see some advance
theory applied.  And congrats on implementing it!

Of course, we'll see if it really boosts query optimization when there's
a full data population.  This view has 3 UNION statements, 11
subselects, 35 JOINS, and calls on 3 custom formatting functions (two of
them many times).  A good "destruction test" for the GEQO, hey?  The
only thing I'm missing is a couple of LEFT OUTER JOINS and maybe a WHERE
NOT EXISTS.

-Josh

P.S. The purpose of the view is to "flatten" a large chunk of complex
relational data into a comma-delimited text table to be imported into an
accounting program.

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Attachment

Re: Eh?

From
Tod McQuillin
Date:
On Mon, 13 Aug 2001, Josh Berkus wrote:

> > > SHould I be concerned about this?
> >
> > > DEBUG:  geqo_main: using edge recombination crossover [ERX]
> >
> > Nope.  See
> > http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/geqo.html
>
> Cool!  I love it when I actually get the chance to see some advance
> theory applied.  And congrats on implementing it!

Yeah, this is really excellent.

> Of course, we'll see if it really boosts query optimization when there's
> a full data population.  This view has 3 UNION statements, 11
> subselects, 35 JOINS, and calls on 3 custom formatting functions ...

This makes me wonder... in the case of a stored complex view, would it be
helpful to ask PostgreSQL to spend extra time in query optimisation and
then cache the result?  Or does it do this already?
-- 
Tod McQuillin




Re: Eh?

From
Tom Lane
Date:
Tod McQuillin <devin@spamcop.net> writes:
> This makes me wonder... in the case of a stored complex view, would it be
> helpful to ask PostgreSQL to spend extra time in query optimisation and
> then cache the result?  Or does it do this already?

I don't see any value in caching plans for views as such.  The planner
considers each query as a whole, which it has to do for performance.
Example:create view v as select * from foo;select * from v where bar = 42;
If there's an index on foo.bar, you'd want this query to use it, no?
So the plan has to be formed on the basis of the actual query; there's
no way to pull out the part for a view.
        regards, tom lane