From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?UTF-8?Q?Juan_Jos=C3=A9_Santamar=C3=ADa_Flecha?=
 <juanjo.santamaria@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: GEQO and join_collapse_limit correlation
In-reply-to: 
 <CAC+AXB3Ug25QUT+hnoZ2bk6w5oFfBbk1qaHu63G1qKm2NO=z1A@mail.gmail.com>
References: 
 <CAC+AXB3Ug25QUT+hnoZ2bk6w5oFfBbk1qaHu63G1qKm2NO=z1A@mail.gmail.com>
Comments: In-reply-to =?UTF-8?Q?Juan_Jos=C3=A9_Santamar=C3=ADa_Flecha?=
 <juanjo.santamaria@gmail.com>
	message dated "Fri, 05 Jan 2018 15:30:25 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <29565.1515168977.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 05 Jan 2018 11:16:17 -0500
Message-ID: <29566.1515168977@sss.pgh.pa.us>
Precedence: bulk

=3D?UTF-8?Q?Juan_Jos=3DC3=3DA9_Santamar=3DC3=3DADa_Flecha?=3D <juanjo.sant=
amaria@gmail.com> writes:
> We recently had an issue in production. We have queries that are
> procedurally generated by an Object/Relational Mapping framework. Some o=
f
> 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 =3D 150
> join_collapse_limit =3D 150
> geqo_threshold =3D 2
> geqo_effort=3D 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