Postgres 外部数据包装器问题与 'is null' where 子句

Question

我正在尝试使用查询构建一个报告，通过FDW访问不同的postgres DB。

我猜为什么它会以这种方式工作。没有 where 子句的第一个查询就可以了：

SELECT s.student_id, p.surname 
FROM rep_student s inner JOIN rep_person p ON p.id = s.person_id

但是添加 caluse 会使此查询慢一百倍(40 秒与 0.1 秒(：

SELECT s.student_id, p.surname 
FROM rep_student s inner JOIN rep_person p ON p.id = s.person_id
WHERE s.learning_end_date IS NULL

详细解释的结果：

Nested Loop  (cost=200.00..226.39 rows=1 width=734)
  Output: s.student_id, p.surname
  Join Filter: ((s.person_id)::text = (p.id)::text)
  ->  Foreign Scan on public.rep_student s  (cost=100.00..111.80 rows=1 width=436)
        Output: s.student_id, s.version, s.person_id, s.curriculum_flow_id, s.learning_start_date, s.learning_end_date, s.learning_end_reason, s.last_update_timestamp, s.aud_created_ts, s.aud_created_by, s.aud_last_updated_ts, s.aud_last_updated_by
        Remote SQL: SELECT student_id, person_id FROM public.rep_student WHERE ((learning_end_date IS NULL))
  ->  Foreign Scan on public.rep_person p  (cost=100.00..113.24 rows=108 width=734)
        Output: p.id, p.version, p.surname, p.name, p.middle_name, p.birthdate, p.info, p.photo, p.last_update_timestamp, p.is_archived, p.gender, p.aud_created_ts, p.aud_created_by, p.aud_last_updated_ts, p.aud_last_updated_by, p.full_name
        Remote SQL: SELECT id, surname FROM public.rep_person`

解释分析的结果：

Nested Loop  (cost=200.00..226.39 rows=1 width=734) (actual time=27.138..38996.303 rows=939 loops=1)
  Join Filter: ((s.person_id)::text = (p.id)::text)
  Rows Removed by Join Filter: 15194898
  ->  Foreign Scan on rep_student s  (cost=100.00..111.80 rows=1 width=436) (actual time=0.685..4.259 rows=939 loops=1)
  ->  Foreign Scan on rep_person p  (cost=100.00..113.24 rows=108 width=734) (actual time=1.380..39.094 rows=16183 loops=939)
Planning time: 0.251 ms
Execution time: 38997.914 ms

表的数据计数相对较小。学生表中几乎所有行learning_end_date列中都有 NULL。

学生 ~ 1000 行。人~15000人。

Postgres似乎在使用FDW过滤NULL时存在问题，因为此查询再次执行速度很快：

SELECT s.student_id, p.surname 
FROM rep_student s inner JOIN rep_person p ON p.id = s.person_id
WHERE s.learning_start_date < current_date

详细解释的结果：

Hash Join  (cost=214.59..231.83 rows=36 width=734)
  Output: s.student_id, p.surname
  Hash Cond: ((s.person_id)::text = (p.id)::text)
  ->  Foreign Scan on public.rep_student s  (cost=100.00..116.65 rows=59 width=436)
        Output: s.student_id, s.version, s.person_id, s.curriculum_flow_id, s.learning_start_date, s.learning_end_date, s.learning_end_reason, s.last_update_timestamp, s.aud_created_ts, s.aud_created_by, s.aud_last_updated_ts, s.aud_last_updated_by
        Filter: (s.learning_start_date < ('now'::cstring)::date)
        Remote SQL: SELECT student_id, person_id, learning_start_date FROM public.rep_student"
  ->  Hash  (cost=113.24..113.24 rows=108 width=734)
        Output: p.surname, p.id
        ->  Foreign Scan on public.rep_person p  (cost=100.00..113.24 rows=108 width=734)
              Output: p.surname, p.id
              Remote SQL: SELECT id, surname FROM public.rep_person`

解释分析的结果：

Hash Join  (cost=214.59..231.83 rows=36 width=734) (actual time=41.614..46.347 rows=940 loops=1)
  Hash Cond: ((s.person_id)::text = (p.id)::text)
  ->  Foreign Scan on rep_student s  (cost=100.00..116.65 rows=59 width=436) (actual time=0.718..3.829 rows=940 loops=1)
        Filter: (learning_start_date < ('now'::cstring)::date)
  ->  Hash  (cost=113.24..113.24 rows=108 width=734) (actual time=40.812..40.812 rows=16183 loops=1)
        Buckets: 16384 (originally 1024)  Batches: 2 (originally 1)  Memory Usage: 921kB
        ->  Foreign Scan on rep_person p  (cost=100.00..113.24 rows=108 width=734) (actual time=2.252..35.079 rows=16183 loops=1)
Planning time: 0.208 ms
Execution time: 47.176 ms

尝试在learning_end_date上添加索引，但没有遇到任何效果。

我需要更改什么才能使用"IS NULL"where 子句更快地执行查询？任何想法将不胜感激！

Answer 1

你的问题是你对这些外来表没有很好的表统计信息，所以PostgreSQL优化器的行数估计是非常随意的。

这会导致优化程序在您报告为缓慢的情况下选择嵌套循环联接，这是一个不合适的计划。

这种情况发生在某种IS NULL条件下只是巧合。

使用 收集外部表的统计信息

ANALYZE rep_student;
ANALYZE rep_person;

那么性能会好得多。

请注意，虽然 autovacuum 会自动收集本地表的统计信息，但它不会为远程表执行此操作，因为它不知道更改了多少行，因此您应该定期ANALYZE数据更改的外部表。