Skip site navigation (1) Skip section navigation (2)

Re: Re: [COMMITTERS] pgsql: Rewrite GEQO's gimme_tree function so that it always finds a

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Re: [COMMITTERS] pgsql: Rewrite GEQO's gimme_tree function so that it always finds a
Date: 2009-11-09 18:42:47
Message-ID: 603c8f070911091042t2bf64c2jcce696eda071d5fb@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-committerspgsql-hackers
On Mon, Nov 9, 2009 at 1:10 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Mon, Nov 9, 2009 at 10:57 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Too bad you don't have debug symbols ... it'd be interesting to see
>>> how long that list is.
>
>> I stopped it a couple of times.  Lengths of list1, list2 respectively:
>
>> 8876, 20
>> 14649, 18
>> 15334, 10
>> 17148, 18
>> 18173, 18
>
> Yowza.  18000 distinct paths for one relation?  Could we see the test
> case?

Well, the test case isn't simple, and I'm not sure that my employer
would be pleased if I posted it to a public mailing list. The general
thrust of it is that there is a view, let's call it foo_view, of the
following form, where foo[1-6] are base tables:

foo1 JOIN bar_view JOIN baz_view JOIN foo3 LEFT JOIN foo4 LEFT JOIN
foo5 LEFT JOIN foo6

bar_view is of the following form, bar[1-14] being base tables:

bar1, bletch_view, bar2, bar3, bar4, bar5, bar6, bar7 LEFT JOIN bar8
LEFT JOIN bar9 LEFT JOIN bar10 LEFT JOIN bar11 LEFT JOIN bar12 LEFT
JOIN bar13 LEFT JOIN bar14

baz_view is of the following form, baz[1-9] being base tables:

baz1, baz2, baz3 JOIN baz4 LEFT JOIN baz5 LEFT JOIN baz6 LEFT JOIN
baz7 LEFT JOIN baz8 LEFT JOIN baz9

bletch_view is of the following form, bletch[1-9] being base tables:

bletch1, bletch2 LEFT JOIN bletch3 LEFT JOIN bletch4 LEFT JOIN bletch5
LEFT JOIN bletch6 LEFT JOIN bletch7 LEFT JOIN bletch8 LEFT JOIN
bletch9

Since the webapp front-end gives users a choice of which columns to
pull down, values from most of these tables can potentially appear in
the output.  There are a handful of rels in bar_view none of whose
attributes can possibly be needed in the output, so I may make a
slightly stripped down version of bar_view just for this purpose, and
keep the original one around for other queries.  I've already done
this for bletch_view, which is a significantly stripped-down version
of a more complex view that is used in other queries.

Most if not all of the joins are from some random column of the
left-hand relation to the primary key of the right-hand relation.
There are no Cartesian products.  Most of the base tables have a
unique index on the primary key and no other indices, although a few
of them have one or two additional indices.

...Robert

In response to

Responses

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2009-11-09 19:14:10
Subject: Re: operator exclusion constraints
Previous:From: Tom LaneDate: 2009-11-09 18:10:11
Subject: Re: Re: [COMMITTERS] pgsql: Rewrite GEQO's gimme_tree function so that it always finds a

pgsql-committers by date

Next:From: User AchernowDate: 2009-11-09 21:58:10
Subject: libpqtypes - libpqtypes: Several bug fixes, add PQregisterComposites,
Previous:From: Tom LaneDate: 2009-11-09 18:38:49
Subject: pgsql: Re-refactor the core scanner's API, in order to get out from

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group