☰

Postgres 外部数据包装器查询使用 now()

tag-icon • tag-icon postgresql
Postgres 外部数据包装器查询使用 now()

我正在尝试使用查询远程数据库postgres_fdw，但当我将其引入查询时，性能确实下降得很严重now()。

EXPLAIN ANALYZE VERBOSE
SELECT *
  FROM remote_schema.my_table
 WHERE remote_table.created_at > '2021-03-21';

得出以下结果：

Foreign Scan on remote_schema.my_table  (cost=100.28..297.35 rows=224 width=1727) (actual time=28.460..28.516 rows=220 loops=1)
  Output: id, created_at, column_2, column_3, column_4"
  Remote SQL: SELECT id, created_at, column_2, column_3, column_4 FROM public.my_table WHERE ((created_at > '2021-03-21 00:00:00-07'::timestamp with time zone))"
Planning Time: 0.906 ms
Execution Time: 28.915 ms

太棒了！

但是，如果我稍微改变查询以使用相对时间，性能就会显著下降：

EXPLAIN ANALYZE VERBOSE
SELECT *
  FROM remote_schema.remote_table
 WHERE remote_table.created_at > (now() - '3 days'::interval)

现在给出了更糟糕的结果：

Foreign Scan on remote_schema.my_table  (cost=100.00..1086.25 rows=216 width=1727) (actual time=18442.738..18443.945 rows=220 loops=1)
  Output: id, created_at, column_2, column_3, column_4"
  Filter: (my_table.created_at > (now() - '3 days'::interval))
  Rows Removed by Filter: 3280
  Remote SQL: SELECT id, created_at, column_2, column_3, column_4 FROM public.my_table"
Planning Time: 0.955 ms
Execution Time: 18444.432 ms

看起来 Postgres 没有意识到该now() - '3 days'::interval子句可以变成常量并传送到远程服务器，而是认为它必须将所有远程数据传输到本地才能进行外部扫描。

有什么方法可以让 Postgres 的第二个查询更加高效？

答案1

我在这里的类似 StackOverflow 帖子中找到了答案：https://stackoverflow.com/a/50167022/237092

诀窍是将其包装now() - '3 days'::interval到子选择中，这显然会说服 Postgres 首先对其进行评估，然后将结果作为常量呈现给查询的其余部分，然后将其推送到远程服务器。

EXPLAIN ANALYZE VERBOSE
SELECT *
  FROM remote_schema.remote_table
 WHERE remote_table.created_at > (SELECT now() - '3 days'::interval)

目前已提供以下优异的性能：

Foreign Scan on remote_schema.my_table  (cost=100.31..879.11 rows=1167 width=1727) (actual time=32.002..32.050 rows=220 loops=1)
   Output: my_table.id, my_table.created_at, my_table.column_2, my_table.column_3, my_table.column_4"
   Remote SQL: SELECT id, created_at, column_2, column_3, column_4 FROM public.my_table WHERE ((created_at > $1::timestamp with time zone))"
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.01 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1)
          Output: (now() - '3 days'::interval)
Planning Time: 3.938 ms
Execution Time: 32.481 ms

相关内容