pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Juan José Santamaría Flecha <juanjo.santamaria@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: GEQO and join_collapse_limit correlation
Date: Fri, 05 Jan 2018 11:16:17 -0500
Message-ID: <29566.1515168977@sss.pgh.pa.us> (raw)
In-Reply-To: <CAC+AXB3Ug25QUT+hnoZ2bk6w5oFfBbk1qaHu63G1qKm2NO=z1A@mail.gmail.com>
References: <CAC+AXB3Ug25QUT+hnoZ2bk6w5oFfBbk1qaHu63G1qKm2NO=z1A@mail.gmail.com>

=?UTF-8?Q?Juan_Jos=C3=A9_Santamar=C3=ADa_Flecha?= <juanjo.santamaria@gmail.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




view thread (5+ messages)  latest in thread

Message-ID: <29566.1515168977@sss.pgh.pa.us>
Permalink:  ../29566.1515168977@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/29566.1515168977@sss.pgh.pa.us

 · 

reply
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: tgl@sss.pgh.pa.us, juanjo.santamaria@gmail.com
  Subject: Re: GEQO and join_collapse_limit correlation
  In-Reply-To: <29566.1515168977@sss.pgh.pa.us>

* 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