Re: [HACKERS] [PATCH] Push limit to sort through a subquery

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

From:

-> 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

To:

-> 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.074 rows=1 loops=1)
         ->  Foreign Scan on t_fdw1  (cost=100.00..193.92 rows=2560 width=8) (actual time=0.928..0.928 rows=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):

Query	original	limit push down
select * from t order by u limit 1	2.276	1.777
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