From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Juan José Santamaría Flecha <juanjo(dot)santamaria(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: GEQO and join_collapse_limit correlation |
Date: | 2018-01-05 16:16:17 |
Message-ID: | 29566.1515168977@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
=?UTF-8?Q?Juan_Jos=C3=A9_Santamar=C3=ADa_Flecha?= <juanjo(dot)santamaria(at)gmail(dot)com> writes:
> We recently had an issue in production. We have queries that are
> procedurally generated by an Object/Relational Mapping framework. Some of
> these queries are huge, involving over 120 tables.
Yeah, you're going to have problems with that :-(
> After some testing in order to lower the planning time we ended bringing
> down the GEQO values, and we have the best results with:
> from_collapse_limit = 150
> join_collapse_limit = 150
> geqo_threshold = 2
> geqo_effort= 2
Hmm. The trouble with this approach is that you're relying on GEQO
to find a good plan, and that's only probabilistic --- especially so
when you're reducing geqo_effort, meaning it doesn't try as many
possibilities as it otherwise might. Basically, therefore, the
fear is that every so often you'll get a bad plan.
If the queries are fairly stylized, you might be able to get good
results by exploiting rather than bypassing join_collapse_limit:
determine what a good join order is, and then write the FROM clause
as an explicit JOIN nest in that order, and then *reduce* not raise
join_collapse_limit to force the planner to follow the syntactic
join order. In this way you'd get rid of most of the run-time
join order search effort. Don't know how cooperative your ORM
would be with such an approach though.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Juan José Santamaría Flecha | 2018-01-05 20:17:06 | Re: GEQO and join_collapse_limit correlation |
Previous Message | Juan José Santamaría Flecha | 2018-01-05 14:30:25 | GEQO and join_collapse_limit correlation |