On 29.04.2017 00:13, Douglas Doole wrote:

    If you add this to the commitfest app, more people might look at
    it when the next commitfest opens.

I have added it. https://commitfest.postgresql.org/14/1119/

    Also, it might help if you can provide a query/ies with numbers
    where this optimization shows improvement.

I can't provide the real queries where we encountered the problem because they are internal. However I showed a simplified version of the queries in my first post.

On our queries, the change made quite a difference - execution time dropped from 31.4 seconds to 7.2 seconds. Explain analyze also shows that memory use dropped significantly and we didn't have to spill the sort to disk


-> Sort (cost=989.95..1013.27 rows=9326 width=30) (node_startup_time/loop=31328.891, node_total_time/loop: 31329.756 rows=2001 loops=1) Buffers: temp read=772 written=11201 lsm_bufmgr hits=3392 Sort Key: *** Sort Method: external merge Sort Space Used: 89592 Sort Space Type: Disk


-> Sort (cost=989.95..1013.27 rows=9326 width=30) (node_startup_time/loop=7123.275, node_total_time/loop: 7123.504 rows=2001 loops=1) Buffers: lsm_bufmgr hits=3387 Sort Key: *** Sort Method: top-N heapsort Sort Space Used: 3256 Sort Space Type: Memory

Attached please find yet another small patch which pushes down LIMIT to ForeignScan. I should notice that currently Postgres optimizer is using "Merge Append" and fetches from remote nodes only required number of tuples. So even without LIMIT push down, postgres_fdw will not pull the whole table from remote host. postgres_fdw is using cursor for fetching data from remote. Default fetch size is 100, so even without limit remote query will fetch no more than 100 rows at remote site.

Assume the following example:

postgres=# create extension postgres_fdw;
postgres=# create server shard1 FOREIGN DATA WRAPPER postgres_fdw options(dbname 'postgres', host 'localhost', port '5432'); postgres=# create server shard2 FOREIGN DATA WRAPPER postgres_fdw options(dbname 'postgres', host 'localhost', port '5432'); postgres=# CREATE USER MAPPING for $user SERVER shard1 options (user '$user'); postgres=# CREATE USER MAPPING for $user SERVER shard1 options (user '$user');
postgres=# CREATE TABLE t(u integer primary key, v integer);
postgres=# CREATE TABLE t1(u integer primary key, v integer);
postgres=# CREATE TABLE t2(u integer primary key, v integer);
postgres=# insert into t1 values (generate_series(1,100000), random()*100000); postgres=# insert into t2 values (generate_series(1,100000), random()*100000); postgres=# CREATE FOREIGN TABLE t_fdw1() inherits (t) server shard1 options(table_name 't1'); postgres=# CREATE FOREIGN TABLE t_fdw2() inherits (t) server shard2 options(table_name 't2');

postgres=# explain analyze select * from t order by u limit 1;
                                                      QUERY PLAN
Limit (cost=200.15..200.20 rows=1 width=8) (actual time=2.010..2.010 rows=1 loops=1) -> Merge Append (cost=200.15..449.39 rows=5121 width=8) (actual time=2.009..2.009 rows=1 loops=1)
         Sort Key: t.u
-> Index Scan using t_pkey on t (cost=0.12..8.14 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1) -> Foreign Scan on t_fdw2 (cost=100.00..193.92 rows=2560 width=8) (actual time=1.074..1.074rows=1 loops=1) -> Foreign Scan on t_fdw1 (cost=100.00..193.92 rows=2560 width=8) (actual time=0.928..0.928rows=1 loops=1)
 Planning time: 0.769 ms
 Execution time: 6.837 ms
(8 rows)

As you can see foreign scan fetches only one row from each remote node.

But still pushing down limit can have positive effect on performance, especially if SORT can be replaced with TOP-N.
I got the following results (time in seconds):

        limit push down
select * from t order by u limit 1
select * from t order by v limit 1
        100     42

There is index for "u", so fetching records with smallest "u" values can be done without sorting, so times are similar. But in case of sorting by "v", pushing down limit allows to use TOP-1 instead of global sort and it reduces query execution time more than 2 times.


Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 080cb0a..e3847ce 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -2949,7 +2949,8 @@ create_cursor(ForeignScanState *node)
 	appendStringInfo(&buf, "DECLARE c%u CURSOR FOR\n%s",
 					 fsstate->cursor_number, fsstate->query);
+	if (node->limit > 0)
+		appendStringInfo(&buf, " LIMIT %lld", (long long)node->limit);
 	 * Notice that we pass NULL for paramTypes, thus forcing the remote server
 	 * to infer types for all parameters.  Since we explicitly cast every
diff --git a/src/backend/executor/nodeLimit.c b/src/backend/executor/nodeLimit.c
index aaec132..675beb5 100644
--- a/src/backend/executor/nodeLimit.c
+++ b/src/backend/executor/nodeLimit.c
@@ -316,10 +316,10 @@ recompute_limits(LimitState *node)
 static void
 pass_down_bound(LimitState *node, PlanState *child_node)
+	int64		tuples_needed = node->count + node->offset;
 	if (IsA(child_node, SortState))
 		SortState  *sortState = (SortState *) child_node;
-		int64		tuples_needed = node->count + node->offset;

 		/* negative test checks for overflow in sum */
 		if (node->noCount || tuples_needed < 0)
@@ -341,6 +341,11 @@ pass_down_bound(LimitState *node, PlanState *child_node)
 		for (i = 0; i < maState->ms_nplans; i++)
 			pass_down_bound(node, maState->mergeplans[i]);
+	else if (IsA(child_node, ForeignScanState))
+	{
+		ForeignScanState  *fsState = (ForeignScanState *) child_node;
+		fsState->limit = tuples_needed;
+	}
 	else if (IsA(child_node, ResultState))
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index d33392f..d706d87 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1502,6 +1502,7 @@ typedef struct ForeignScanState
 	ScanState	ss;				/* its first field is NodeTag */
 	ExprState  *fdw_recheck_quals;		/* original quals not in ss.ps.qual */
 	Size		pscan_len;		/* size of parallel coordination information */
+	int64		limit;			/* pushed down limit */
 	/* use struct pointer to avoid including fdwapi.h here */
 	struct FdwRoutine *fdwroutine;
 	void	   *fdw_state;		/* foreign-data wrapper can keep state here */
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to