Re: [HACKERS] [PATCH] Incremental sort

Hi!

On Fri, Dec 1, 2017 at 11:39 AM, Antonin Houska <ah(at)cybertec(dot)at> wrote:

> I expected the number of groups actually that actually appear in the
> output,
> you consider it the number of groups started. I can't find similar case
> elsewhere in the code (e.g. Agg node does not report this kind of
> information), so I have no clue. Someone else will have to decide.
>

OK.

> But there is IncrementalSort node on the remote side.
> > Let's see what happens. Idea of "CROSS JOIN, not pushed down" test is
> that cross join with ORDER BY LIMIT is not beneficial to push down, because
> LIMIT is not pushed down and remote side wouldn't be able to use top-N
> heapsort. But if remote side has incremental sort then it can be
> > used, and fetching first 110 rows is cheap. Let's see plan of original
> "CROSS JOIN, not pushed down" test with incremental sort.
> >
> > # EXPLAIN (ANALYZE, VERBOSE) SELECT t1.c3, t2.c3 FROM ft1 t1 CROSS JOIN
> ft2 t2 ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
>
> ok, understood, thanks. Perhaps it's worth a comment in the test script.
>
> I'm afraid I still see a problem. The diff removes a query that (although a
> bit different from the one above) lets the CROSS JOIN to be pushed down and
> does introduce the IncrementalSort in the remote database. This query is
> replaced with one that does not allow for the join push down.
>
> *** a/contrib/postgres_fdw/sql/postgres_fdw.sql
> --- b/contrib/postgres_fdw/sql/postgres_fdw.sql
> *************** SELECT t1.c1 FROM ft1 t1 WHERE NOT EXIST
> *** 510,517 ****
> SELECT t1.c1 FROM ft1 t1 WHERE NOT EXISTS (SELECT 1 FROM ft2 t2 WHERE
> t1.c1 = t2.c2) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
> -- CROSS JOIN, not pushed down
> EXPLAIN (VERBOSE, COSTS OFF)
> ! SELECT t1.c1, t2.c1 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c1, t2.c1
> OFFSET 100 LIMIT 10;
> ! SELECT t1.c1, t2.c1 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c1, t2.c1
> OFFSET 100 LIMIT 10;
> -- different server, not pushed down. No result expected.
> EXPLAIN (VERBOSE, COSTS OFF)
> SELECT t1.c1, t2.c1 FROM ft5 t1 JOIN ft6 t2 ON (t1.c1 = t2.c1) ORDER BY
> t1.c1, t2.c1 OFFSET 100 LIMIT 10;
> --- 510,517 ----
> SELECT t1.c1 FROM ft1 t1 WHERE NOT EXISTS (SELECT 1 FROM ft2 t2 WHERE
> t1.c1 = t2.c2) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
> -- CROSS JOIN, not pushed down
> EXPLAIN (VERBOSE, COSTS OFF)
> ! SELECT t1.c3, t2.c3 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c3, t2.c3
> OFFSET 100 LIMIT 10;
> ! SELECT t1.c3, t2.c3 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c3, t2.c3
> OFFSET 100 LIMIT 10;
> -- different server, not pushed down. No result expected.
> EXPLAIN (VERBOSE, COSTS OFF)
> SELECT t1.c1, t2.c1 FROM ft5 t1 JOIN ft6 t2 ON (t1.c1 = t2.c1) ORDER BY
> t1.c1, t2.c1 OFFSET 100 LIMIT 10;
>
> Shouldn't the test contain *both* cases?

Thank you for pointing that. Sure, both cases are better. I've added
second case as well as comments. Patch is attached.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company