From: | Alexander Pyhalov <a(dot)pyhalov(at)postgrespro(dot)ru> |
---|---|
To: | Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Push down time-related SQLValue functions to foreign server |
Date: | 2021-08-19 09:51:55 |
Message-ID: | 39f983a265780b378b835af602276d0a@postgrespro.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi.
The attached patches allow pushing down
current_timestamp/localtimestamp/current_time/localtime and now() to
remote PostgreSQL server as locally computed parameters.
The idea is based on oracle_fdw behavior.
Examples.
\d test
Foreign table "public.test"
Column | Type | Collation | Nullable | Default |
FDW options
--------+--------------------------+-----------+----------+---------+-------------------
i | integer | | | |
(column_name 'i')
t | timestamp with time zone | | | |
(column_name 't')
Server: loopback
FDW options: (schema_name 'data', table_name 'test')
Prior the patch:
explain verbose select * from test where t=current_timestamp;
QUERY PLAN
---------------------------------------------------------------------
Foreign Scan on public.test (cost=100.00..188.12 rows=11 width=12)
Output: i, t
Filter: (test.t = CURRENT_TIMESTAMP)
Remote SQL: SELECT i, t FROM data.test
explain verbose update test set t=current_timestamp where t<now();
QUERY PLAN
----------------------------------------------------------------------------
Update on public.test (cost=100.00..154.47 rows=0 width=0)
Remote SQL: UPDATE data.test SET t = $2 WHERE ctid = $1
-> Foreign Scan on public.test (cost=100.00..154.47 rows=414
width=50)
Output: CURRENT_TIMESTAMP, ctid, test.*
Filter: (test.t < now())
Remote SQL: SELECT i, t, ctid FROM data.test FOR UPDATE
After patch:
explain verbose select * from test where t=current_timestamp;
QUERY PLAN
-------------------------------------------------------------------------------------
Foreign Scan on public.test (cost=100.00..144.35 rows=11 width=12)
Output: i, t
Remote SQL: SELECT i, t FROM data.test WHERE ((t = $1::timestamp with
time zone))
explain verbose update test set t=current_timestamp where t<now();
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Update on public.test (cost=100.00..137.93 rows=0 width=0)
-> Foreign Update on public.test (cost=100.00..137.93 rows=414
width=50)
Remote SQL: UPDATE data.test SET t = $1::timestamp with time
zone WHERE ((t < $1::timestamp with time zone))
--
Best regards,
Alexander Pyhalov,
Postgres Professional
Attachment | Content-Type | Size |
---|---|---|
0001-SQLValue-functions-pushdown.patch | text/x-diff | 15.4 KB |
0002-now-pushdown.patch | text/x-diff | 6.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Hannu Krosing | 2021-08-19 09:57:58 | Re: Middleware Messages for FE/BE |
Previous Message | Hannu Krosing | 2021-08-19 09:36:47 | Re: Middleware Messages for FE/BE |