I've set up a PostgreSQL FDW server with the following table, sharded by user_id
over four servers:
CREATE TABLE my_big_table
(
user_id bigint NOT NULL,
serial bigint NOT NULL, -- external, incrementing only
some_object_id bigint NOT NULL,
timestamp_ns bigint NOT NULL,
object_type smallint NOT NULL,
other_type smallint NOT NULL,
data bytea
) PARTITION BY HASH (user_id) ;
CREATE SERVER shardA
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.200.11', port '5432', dbname 'postgres', fetch_size '10000');
.
.
.
CREATE SERVER shardD
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.200.14', port '5432', dbname 'postgres', fetch_size '10000');
create foreign table my_big_table_mod4_s0 partition of my_big_table
FOR VALUES WITH (MODULUS 4, REMAINDER 0) server shardA
OPTIONS (table_name 'my_big_table_mod4_s0');
.
.
.
create foreign table my_big_table_mod4_s3 partition of my_big_table
FOR VALUES WITH (MODULUS 4, REMAINDER 3) server shardD
OPTIONS (table_name 'my_big_table_mod4_s3');
On the backend servers I have set up a table with several indexes, its data CLUSTERed by (user_id, serial)
across several partitions. I don't think those details are very relevant for my actual question, though.
The common query against my cluster is in the pattern of:
SELECT * from my_big_table
WHERE
user_id = 12345 -- only 1 user, always! --> single foreign server.
ORDER BY serial DESC -- get 'newest' 90% of the time, 10% ASC
LIMIT 1000; -- max limit 1000, sometimes less
For users with < 1000 records: all fine, no problem.
For users with > 100.000 records I see the issue leading to poor performance: explain
shows LIMIT and sorting happens on FDW, not pushed down. Why?
Limit (cost=927393.08..927395.58 rows=1000 width=32)
Output: my_big_table_mod4_s0.serial, my_big_table_mod4_s0.some_object_id, my_big_table_mod4_s0.timestamp_ns, my_big_table_mod4_s0.object_type, my_big_table_mod4_s0.other_type, (length(my_big_table_mod4_s0.data))
-> Sort (cost=927393.08..931177.06 rows=1513592 width=32)
Output: my_big_table_mod4_s0.serial, my_big_table_mod4_s0.some_object_id, my_big_table_mod4_s0.timestamp_ns, my_big_table_mod4_s0.object_type, my_big_table_mod4_s0.other_type, (length(my_big_table_mod4_s0.data))
Sort Key: my_big_table_mod4_s0.serial DESC
-> Foreign Scan on public.my_big_table_mod4_s0 (cost=5318.35..844404.46 rows=1513592 width=32)
Output: my_big_table_mod4_s0.serial, my_big_table_mod4_s0.some_object_id, my_big_table_mod4_s0.timestamp_ns, my_big_table_mod4_s0.object_type, my_big_table_mod4_s0.other_type, length(my_big_table_mod4_s0.data)
Remote SQL: SELECT serial, some_object_id, timestamp_ns, object_type, other_type, data FROM public.my_big_table_mod4_s0 WHERE ((user_id = 4560084))
JIT:
Functions: 3
Options: Inlining true, Optimization true, Expressions true, Deforming true
Takeaway from the above is:
- Single backend server selected: OK! (solved with this)
Remote SQL: SELECT [...]
indicates no ORDER BY, no LIMIT. Problem.
Executed on the backend server directly shows this:
Limit (cost=1.74..821.42 rows=1000 width=32)
Output: my_big_table_mod4_s0_part123.serial, my_big_table_mod4_s0_part123.some_object_id, my_big_table_mod4_s0_part123.timestamp_ns, my_big_table_mod4_s0_part123.object_type, my_big_table_mod4_s0_part123.other_type, (length(my_big_table_mod4_s0_part123.data))
-> Append (cost=1.74..1240669.45 rows=1513592 width=32)
-> Index Scan Backward using my_big_table_mod4_s0_part123_pkey on public.my_big_table_mod4_s0_part123 (cost=0.43..290535.67 rows=355620 width=32)
Output: my_big_table_mod4_s0_part123.serial, my_big_table_mod4_s0_part123.some_object_id, my_big_table_mod4_s0_part123.timestamp_ns, my_big_table_mod4_s0_part123.object_type, my_big_table_mod4_s0_part123.other_type, length(my_big_table_mod4_s0_part123.data)
Index Cond: (my_big_table_mod4_s0_part123.user_id = 4560084)
-> Index Scan Backward using [... other partitions ...]
What have I tried:
- As FDW is still active in development, I tried using a newer version: both 11.4 and 12-beta2 for both FDW as well as backend servers. No difference observed.
- Running ANALYZE against the foreign table (on FDW instance). Takes a huge amount of time; looks like it's full tablescanning the remote table? No difference in query planning.
- Changing the value of
fetch_size
on the remote SERVER object. No difference. - Set
use_remote_estimate=true
on the remote SERVER object. No difference. - Set
fdw_tuple_cost=100
on the remote SERVER object. Sorting now happens on the remote server, but LIMIT still not pushed down. Looking online for other people seeing this, only showing this related post: Missed LIMIT clause pushdown in FDW API
But this thread mentions commits fixing this in 9.7 and whatnot, but I'm using 11.4 and 12-beta2. Or do I misunderstand this?
And post: Strange cost estimation for foreign tables shows a nice example on tuning FDW, but unfortunately does not cover my issue with LIMITs.
Having a quick look in the PostgreSQL source code, I noticed this statement, perhaps relevant for FDW, maybe not (source).
We can't push sub-select containing LIMIT/OFFSET to workers as there is no guarantee that the row order will be fully deterministic, and applying LIMIT/OFFSET will lead to inconsistent results at the top-level. (In some cases, where the result is ordered, we could relax this restriction. But it doesn't currently seem worth expending extra effort to do so.)
Having another look at the source code, I found this promising commit (d50d172e51):
This provides the ability for postgres_fdw to handle SELECT commands so that it 1) skips the LockRows step (if any) (note that this is safe since it performs early locking) and 2) pushes down the LIMIT and/or OFFSET restrictions (if any) to the remote side. This doesn't handle the INSERT/UPDATE/DELETE cases.
It adds a unit test case exactly for my case!
-- and both ORDER BY and LIMIT can be shipped EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(public.===) c2)) ORDER BY c2 ASC NULLS LAST LIMIT 1::bigint
which should be part of 12-beta2, which I'm running already...
I noticed I should use indexes in DESC order here, but that's not so relevant now.