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

On Mon, Nov 9, 2009 at 10:18 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> I think I just ran smack dab into this bug on 8.3.8 (RPM:
> postgresql-8.3.8-1.fc10.i386).  I had a query that wasn't coming out
> very well with the default settings so I raised the collapse limits
> and let GEQO have a crack at it.  This was not a rousing success.
> It didn't actually fail, but it did this sort of thing for a real long
> time.
>
> #0  0x08192c6b in bms_overlap ()
> #1  0x081b6046 in have_join_order_restriction ()
> #2  0x081a9438 in gimme_tree ()
> #3  0x081a9515 in geqo_eval ()
> #4  0x081a9d6c in random_init_pool ()
> #5  0x081a9728 in geqo ()
[snip]

That backtrace actually bounced around a little bit - it was always in
gimme_tree(), but the rest varied. If I raise the collapse limits AND
disable GEQO, then I get backtraces that CONSISTENTLY look like this.

#0 0x081923bc in list_concat_unique_ptr ()
#1 0x081b6922 in join_search_one_level ()
#2 0x081ab22b in standard_join_search ()
#3 0x081abf72 in ?? ()
#4 0x081be4c3 in query_planner ()
#5 0x081beedb in ?? ()
#6 0x081c00ce in subquery_planner ()
#7 0x081c057c in standard_planner ()
#8 0x08207caa in pg_plan_query ()
#9 0x08207df4 in pg_plan_queries ()
#10 0x082083d4 in ?? ()
#11 0x08209b3f in PostgresMain ()
#12 0x081dc1e5 in ?? ()
#13 0x081dd20a in PostmasterMain ()
#14 0x08190f96 in main ()

I've stopped the query more than 10 times now and EVERY SINGLE ONE
finds it in list_concat_unique_ptr(). :-(

It's also using about 12x as much RAM as the GEQO version.

...Robert