Re: Limit and inherited tables

On Fri, Jan 15, 2016 at 9:47 PM, Konstantin Knizhnik <
k(dot)knizhnik(at)postgrespro(dot)ru> wrote:

>
> This example is lacking indexes on the child tables, which is
>> why the plan shown is about as good as you're going to get.
>> The contents of foo1 and foo2 have to be read in entirety in any
>> case, and sorting them separately is not a win compared to doing
>> a single sort.
>>
> It is true, but not in case of FDW connected to remote host.
> In this case sending large volumes of data through network will be very
> inefficient.
>
> There will be no problem if FDW can provide index scan - in this case
> MergeAppend will fetch only required number of records:
>
> postgres=# explain analyze select * from t order by u limit 1;
> QUERY PLAN
>
> -----------------------------------------------------------------------------------------------------------------------
> Limit (cost=300.17..300.23 rows=1 width=8) (actual time=4.588..4.588
> rows=1 loops=1)
> -> Merge Append (cost=300.17..762.76 rows=7681 width=8) (actual
> time=4.586..4.586 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.003..0.003 rows=0 loops=1)
> -> Foreign Scan on t_fdw1 (cost=100.00..193.92 rows=2560
> width=8) (actual time=1.532..1.532 rows=1 loops=1)
> -> Foreign Scan on t_fdw2 (cost=100.00..193.92 rows=2560
> width=8) (actual time=1.510..1.510 rows=1 loops=1)
> -> Foreign Scan on t_fdw3 (cost=100.00..193.92 rows=2560
> width=8) (actual time=1.535..1.535 rows=1 loops=1)
>
> But if sort is performed by non-indexed fields, then current behaviour
> will be inefficient and can be significantly improved by pushing limits to
> remote hosts.
>
>
Pushing ORDER BY clause to the foreign server is supported with commit
f18c944b6137329ac4a6b2dce5745c5dc21a8578. Check if that helps to get sorted
data from the foreign server. LIMIT pushdown is not supported yet, though.

>
> --
> Konstantin Knizhnik
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company