Hi! On Fri, Dec 1, 2017 at 11:39 AM, Antonin Houska <a...@cybertec.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
incremental-sort-12.patch
Description: Binary data
