From: Jonah H. Harris <jonah.harris@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pryscila.lista@gmail.com
Cc: pgsql-hackers@postgresql.org
Subject: Re: About method of PostgreSQL's Optimizer
Date: Thu, 15 Sep 2005 01:08:47 -0400
Message-ID: <36e682920509142208211344d5@mail.gmail.com> (raw)
In-Reply-To: <36e6829205091421394fbb062a@mail.gmail.com>
References: <cf0868bd05091315503024742@mail.gmail.com>
<cf0868bd05091317412310c490@mail.gmail.com>
<36e6829205091320085993ceba@mail.gmail.com>
<cf0868bd050914085233eae603@mail.gmail.com>
<36e6829205091409361035d32a@mail.gmail.com>
<6127.1126720486@sss.pgh.pa.us>
<36e6829205091421394fbb062a@mail.gmail.com>
Pryscila,
For research reference, you may want to look at the work done on the
Columbia Query Optimization Framework. As I recall, I think it (or its
predecessors) had both cost and rule-based optimization. If you need the
code to it, I can dig it up on one of my old systems.
Albeit dated, another good reference for optimizer implementation is the
cascades query optimization framework.
On 9/15/05, Jonah H. Harris <jonah.harris@gmail.com> wrote:
>
> Tom,
>
> I agree. There have been several occasions where GEQO has performed poorly
> for me. I'll search the archives for the past discussions.
>
> sorry for sending this to you twice Tom... forgot to hit reply all :(
>
> On 9/14/05, Tom Lane <tgl@sss.pgh.pa.us > wrote:
> >
> > "Jonah H. Harris" <jonah.harris@gmail.com > writes:
> > > As for using both in the same optimizer, I could only see an algorithm
> > such
> > > as a customized-A* being used to planning *some* large queries. The
> > reason I
> > > say this is because the cost calculation, which would still need to be
> >
> > > breadth-first, could calculate and cache the cost of most nodes
> > thereby
> > > allowing you to possibly perform transformations at the tail of
> > calculation.
> >
> > We do already have two different plan search algorithms: the strict
> > bottom-up dynamic programming approach (System R style) and the GEQO
> > optimizer, which we switch to when there are too many joins needed to
> > allow exhaustive search. The GEQO code still depends on the normal
> > plan cost estimation code, but it doesn't consider every possible plan.
> >
> > I've never been very happy with the GEQO code: the random component of
> > the algorithm means you get unpredictable (and sometimes awful) plans,
> > and the particular variant that we are using is really designed to solve
> >
> > traveling-salesman problems. It's at best a poor fit to the join
> > planning problem.
> >
> > So it seems interesting to me to think about replacing GEQO with a
> > rule-based optimizer for large join search spaces.
> >
> > There are previous discussions about this in the archives, I believe.
> >
> > regards, tom lane
> >
>
>
>
> --
> Respectfully,
>
> Jonah H. Harris, Database Internals Architect
> EnterpriseDB Corporation
> http://www.enterprisedb.com/
>
--
Respectfully,
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
http://www.enterprisedb.com/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jonah.harris@gmail.com, tgl@sss.pgh.pa.us, pryscila.lista@gmail.com, pgsql-hackers@postgresql.org
Subject: Re: About method of PostgreSQL's Optimizer
In-Reply-To: <36e682920509142208211344d5@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox