时间戳

首页 > 解决方案 > 添加限制子句时插入选择获得更好的计划

postgresql - 添加限制子句时插入选择获得更好的计划

问题描述

这是我正在运行的服务器

select version();
                                                 version
---------------------------------------------------------------------------    
PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)

我从编写选择开始（ext.t_event 和 ext.t_event_data 是 oracle_fdw（1.1 版）从远程 oracle 数据库获取的两个外部表）

select 
  te.id_data, 
  te.id_device, 
  te.date_write, 
  te.date_event, 
  ted.i_inout, 
  ted.value
from ext.t_event te, ext.t_event_data ted 
where te.id_device =2749651 
  and te.date_event >= '2019-01-16'and te.date_event < '2019-01-17' 
  and te.id_data=ted.id_data;

获取整个记录集（3600 条记录）大约需要 10 秒。

但后来我把选择变成了插入选择

insert into stg_data
select 
  te.id_data, 
  te.id_device, 
  te.date_write, 
  te.date_event, 
  ted.i_inout, 
  ted.value
from ext.t_event te, ext.t_event_data ted 
where te.id_device =2749651 
  and te.date_event >= '2019-01-16'and te.date_event < '2019-01-17' 
  and te.id_data=ted.id_data;

我被迫终止查询，它已经运行了 30 多分钟！

经过几个小时的挣扎和绝望的尝试，我决定试试这个

insert into stg_data
select 
  te.id_data, 
  te.id_device, 
  te.date_write, 
  te.date_event, 
  ted.i_inout, 
  ted.value
from ext.t_event te, ext.t_event_data ted 
where te.id_device =2749651 
  and te.date_event >= '2019-01-16'and te.date_event < '2019-01-17' 
  and te.id_data=ted.id_data
  limit 5000;

并且......在 20 秒内让我感到惊讶，我将整个记录集存储在 stg_data 中。

为了更好地理解差异，我决定分析这些计划。

选择无限制

 Foreign Scan  (cost=10000.00..20000.00 rows=1000 width=548)
   Oracle query: SELECT /*eb01c463a72c3b6350f86f5db25e1353*/ r1."ID_DATA",
   r1."ID_DEVICE", r1."DATE_WRITE", r1."DATE_EVENT", r2."I_INOUT",
   r2."VALUE" FROM ("DISPATCH"."T_EVENT" r1 INNER JOIN
   "DISPATCH"."T_EVENT_DATA" r2 ON (r1."ID_DATA" = r2."ID_DATA") AND
  (r1."DATE_EVENT" >= (CAST ('2019-01-16 00:00:00.000000 AD' AS
  TIMESTAMP))) AND (r1."DATE_EVENT" < 
  (CAST ('2019-01-17 00:00:00.000000 AD' AS TIMESTAMP))) 
  AND (r1."ID_DEVICE" = 2749651))

有限制地选择

 Limit  (cost=10000.00..20000.00 rows=1000 width=548)
   ->  Foreign Scan  (cost=10000.00..20000.00 rows=1000 width=548)
      Oracle query: SELECT /*eb01c463a72c3b6350f86f5db25e1353*/
      r1."ID_DATA", r1."ID_DEVICE", r1."DATE_WRITE", r1."DATE_EVENT", 
      r2."I_INOUT", r2."VALUE" FROM ("DISPATCH"."T_EVENT" r1 INNER 
      JOIN "DISPATCH"."T_EVENT_DATA" r2 ON (r1."ID_DATA" = r2."ID_DATA")
      AND (r1."DATE_EVENT" >= (CAST ('2019-01-16 00:00:00.000000 AD' AS 
      TIMESTAMP))) AND (r1."DATE_EVENT" < (CAST ('2019-01-17
      00:00:00.000000 AD' AS TIMESTAMP))) AND (r1."ID_DEVICE" = 2749651))

因此，它基本上向 Oracle 发送相同的查询，并在获取完成后立即在本地应用 FILTER。

INSER-SELECT 计划看起来一样吗？不！

带有 LIMIT 的 INSERT_SELECT

Insert on stg_data_hist  (cost=10000.00..20010.00 rows=1000 width=548)
   ->  Limit  (cost=10000.00..20000.00 rows=1000 width=548)
         ->  Foreign Scan  (cost=10000.00..20000.00 rows=1000 width=548)
               Oracle query: SELECT /*eb01c463a72c3b6350f86f5db25e1353*/ 
               r1."ID_DATA", r1."ID_DEVICE", r1."DATE_WRITE", 
               r1."DATE_EVENT", r2."I_INOUT", r2."VALUE" FROM 
               ("DISPATCH"."T_EVENT" r1 INNER JOIN 
               "DISPATCH"."T_EVENT_DATA" r2 ON (r1."ID_DATA" = 
               r2."ID_DATA") AND (r1."DATE_EVENT" >= (CAST ('2019-01-16 
               00:00:00.000000 AD' AS TIMESTAMP))) AND (r1."DATE_EVENT" < 
               (CAST('2019-01-17 00:00:00.000000 AD' AS TIMESTAMP))) AND 
               (r1."ID_DEVICE" = 2749651))

INSERT-SELECT 无 LIMIT 子句

Insert on stg_data_hist  (cost=30012.50..40190.00 rows=5000 width=548)
 ->  Hash Join  (cost=30012.50..40190.00 rows=5000 width=548)
       Hash Cond: (te.id_data = ted.id_data)
     ->  Foreign Scan on t_event te  (cost=10000.00..20000.00 rows=1000 width=28)
           Oracle query: SELECT /*93379c271b3f1bc08a1dbb94fb89f739*/ 
           r3."ID_DATA", r3."ID_DEVICE", r3."DATE_WRITE", r3."DATE_EVENT" 
           FROM "DISPATCH"."T_EVENT" r3 WHERE (r3."DATE_EVENT" >= 
           (CAST ('2019-01-16 00:00:00.000000 AD' AS TIMESTAMP))) AND 
           (r3."DATE_EVENT" < (CAST ('2019-01-17 00:00:00.000000 AD' AS 
           TIMESTAMP))) AND (r3."ID_DEVICE" = 2749651)
       ->  Hash  (cost=20000.00..20000.00 rows=1000 width=528)
           ->  Foreign Scan on t_event_data ted  
                  (cost=10000.00..20000.00 rows=1000 width=528)
                 Oracle query: SELECT /*21c8741f2fa8a8d13d037c3191e8ac96*/ 
                    r4."ID_DATA", r4."I_INOUT", r4."VALUE" FROM 
                    "DISPATCH"."T_EVENT_DATA" r4

这就解释了为什么它比另一个需要更长的时间。它从一个外部表中检索日期过滤的记录，从第二个外部表中检索完整集并在本地进行连接。那将需要很长时间！这是几百万条记录而不是几千条记录。

最后我的两个问题

1）我想制定第一个计划，但要摆脱 LIMIT 条款（让我的脊椎发抖:-)）。你会怎么做？除了 join 子句外，我无意对 ext.t_event_data 应用过滤器。

2) 为什么两个 INSERT-SELECT 计划看起来如此不同，尽管这两个 SELECT 计划看起来如此相似？

感谢阅读，祝您有美好的一天

标签： postgresqlsql-execution-planinsert-selectforeign-data-wrapper

解决方案

计划者似乎认为它只会得到几千行，这显然是有问题的，通过运行'ANALYZE ext.t_event'确保外部表的统计信息是最新的，对于 ext.t_event_data 也是如此，因为：

https://github.com/laurenz/oracle_fdw

PostgreSQL 不会使用 autovacuum 守护进程自动收集外部表的统计信息。

请记住，分析 Oracle 外部表将导致完整的顺序表扫描。您可以使用表选项 sample_percent 通过仅使用 Oracle 表的样本来加快速度。

如果使用限制，则在选择情况下和插入情况下将连接下推到 Oracle，所以我可以看到在无限制的插入中不使用它的唯一原因是缺乏精确的表统计信息。您可以尝试将插入查询重写为 CTE（由于明显原因尚未测试此查询）：

WITH foreign_data AS (
select 
  te.id_data, 
  te.id_device, 
  te.date_write, 
  te.date_event, 
  ted.i_inout, 
  ted.value
from ext.t_event te, ext.t_event_data ted 
where te.id_device =2749651 
  and te.date_event >= '2019-01-16'and te.date_event < '2019-01-17' 
  and te.id_data=ted.id_data
)

insert into stg_data from foreign_data

您还可以尝试将查询重写为显式内部联接，而不是在 where 子句中使用联接条件 (te.id_data=ted.id_data)。

推荐阅读