From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jon Nelson <jnelson+pgsql@jamponi.net>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: Peter Geoghegan <peter@2ndquadrant.com>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: Craig Ringer <craig@2ndquadrant.com>
Cc: David Greco <David_Greco@harte-hanks.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: SOLVED - RE: Poor performance using CTE
Date: Tue, 20 Nov 2012 16:26:49 -0500
Message-ID: <15381.1353446809@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKuK5J1aR1_KvOr6-q_QLm_2PKw=W690Zbg=32WKMidpOLqWiA@mail.gmail.com>
References: <187F6C10D2931A4386EE8E58E13857F61291D897@BY2PRD0811MB415.namprd08.prod.outlook.com>
<20462.1352910543@sss.pgh.pa.us>
<50A442A8.10803@2ndQuadrant.com>
<50A4497D.80902@dunslane.net>
<CAEYLb_XLgrFBMM5Ce9ZBoa0K+NY1hpFXyvXeaf6yWbHOjn3-3g@mail.gmail.com>
<CAHyXU0yPcfWQ58yU07vhit2PQ70kcxsDOr7QumZgvnKT=fcC-Q@mail.gmail.com>
<CAKuK5J1aR1_KvOr6-q_QLm_2PKw=W690Zbg=32WKMidpOLqWiA@mail.gmail.com>
Jon Nelson <jnelson+pgsql@jamponi.net> writes:
> ... Perhaps even including a
> small blurb about what an optimization barrier even means (my
> understanding is that it merely forces materialization of that part of
> the query).
FWIW, it has nothing to do with materialization; it means that we don't
push conditions down into that subquery, nor pull subexpressions up out
of it, nor rearrange join order across the subquery boundary. In short
the subquery is planned separately from the outer query. But it could
then be run by the executor in the usual tuple-at-a-time fashion,
without materializing the whole subquery result.
It is true that CTEScan nodes materialize the subquery output (ie copy
it into a tuplestore), but that's to support multiple CTEScans reading
the same CTE. One of the optimizations we *should* put in place
sometime is skipping the tuplestore if there's only one CTEScan on the
CTE.
regards, tom lane
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, jnelson+pgsql@jamponi.net, mmoncure@gmail.com, peter@2ndquadrant.com, andrew@dunslane.net, craig@2ndquadrant.com, David_Greco@harte-hanks.com
Subject: Re: SOLVED - RE: Poor performance using CTE
In-Reply-To: <15381.1353446809@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