From: David Fetter <david@fetter.org>
To: Andrew Gierth <andrew@tao11.riddles.org.uk>
Cc: Andreas Karlsson <andreas@proxel.se>
Cc: Thomas Munro <thomas.munro@enterprisedb.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Early WIP/PoC for inlining CTEs
Date: Sat, 6 Oct 2018 00:43:11 +0200
Message-ID: <20181005224310.GR25294@fetter.org> (raw)
In-Reply-To: <87y3bdckvu.fsf@news-spur.riddles.org.uk>
References: <20180725054236.GB17411@fetter.org>
<87bmavfjhy.fsf@news-spur.riddles.org.uk>
<20180725191447.GE17411@fetter.org>
<CAEepm=0MKV3MnR4Dn5RkXsK+RnmHEFitqiG4PcBhXtBK4JOoWw@mail.gmail.com>
<20180727081058.GN17411@fetter.org>
<dccb37dd-2f52-dbd7-a4c4-72a5853f4838@proxel.se>
<e02af96a-3e25-5de4-09a4-a7ebf3452184@proxel.se>
<20181003155720.GG25294@fetter.org>
<5bad579d-7a99-74de-2311-b4f049da8b40@proxel.se>
<87y3bdckvu.fsf@news-spur.riddles.org.uk>
On Fri, Oct 05, 2018 at 01:40:05AM +0100, Andrew Gierth wrote:
> >>>>> "Andreas" == Andreas Karlsson <andreas@proxel.se> writes:
>
> > On 10/03/2018 05:57 PM, David Fetter wrote:
> >> Is there any meaningful distinction between "inlining," by which I
> >> mean converting to a subquery, and predicate pushdown, which
> >> would happen at least for a first cut, at the rewrite stage?
>
> Yes.
>
> Andreas> Sorry, but I do not think I understand your question. The
> Andreas> ability to push down predicates is just one of the potential
> Andreas> benefits from inlining.
>
> Consider the difference between (in the absence of CTE inlining):
>
> -- inline subquery with no optimization barrier (qual may be pushed down)
> select * from (select x from y) s where x=1;
...and doesn't need to materialize all of y,
> -- inline subquery with optimization barrier (qual not pushed down)
> select * from (select x from y offset 0) s where x=1;
>
> -- CTE with materialization
> with s as (select x from y) select * from s where x=1;
while both of these do. I was interested to discover that on my
synthetic test of 10 million integers from generate_series(1,10000000)
both with and without a b-tree index on x--as expected, the index has
no effect--I consistently get stuff like this:
shackle@[local]:5432/shackle(10.5)(18539) > explain (analyze, verbose, costs on, buffers on, timing on) with s as (select x from y) select * from s where x=1;
QUERY PLAN
══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
CTE Scan on s (cost=144247.77..369247.25 rows=50000 width=4) (actual time=0.213..2287.355 rows=1 loops=1)
Output: s.x
Filter: (s.x = 1)
Rows Removed by Filter: 9999999
Buffers: shared hit=16310 read=27938, temp written=17089
CTE s
-> Seq Scan on public.y (cost=0.00..144247.77 rows=9999977 width=4) (actual time=0.208..593.426 rows=10000000 loops=1)
Output: y.x
Buffers: shared hit=16310 read=27938
Planning time: 0.110 ms
Execution time: 2313.682 ms
(11 rows)
shackle@[local]:5432/shackle(10.5)(18539) > explain (analyze, verbose, costs on, buffers on, timing on) select * from (select x from y offset 0) s where x=1;
QUERY PLAN
════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
Subquery Scan on s (cost=0.00..269247.48 rows=1 width=4) (actual time=0.734..1069.012 rows=1 loops=1)
Output: s.x
Filter: (s.x = 1)
Rows Removed by Filter: 9999999
Buffers: shared hit=16316 read=27932
-> Seq Scan on public.y (cost=0.00..144247.77 rows=9999977 width=4) (actual time=0.731..539.463 rows=10000000 loops=1)
Output: y.x
Buffers: shared hit=16316 read=27932
Planning time: 0.114 ms
Execution time: 1069.032 ms
(10 rows)
i.e. for this case, the CTE scan takes over 2.3x the time the simple
materialization does. Also, when I boost work_mem to 1GB (256MB wasn't
enough to avoid "temp written"), there's still a 1.8x penalty.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
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-hackers@postgresql.org
Cc: david@fetter.org, andrew@tao11.riddles.org.uk, andreas@proxel.se, thomas.munro@enterprisedb.com
Subject: Re: Early WIP/PoC for inlining CTEs
In-Reply-To: <20181005224310.GR25294@fetter.org>
* 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