Postgres优化器为外部联接选择了错误的执行计划
Postgres优化器为外部联接选择了错误的执行计划
提问于 2016-02-24 15:32:15
我有以下数据模型：
具有~310 M行的父表：
Table parent:
Column | Type
------------+-------------------------------
id | BIGINT (Primary key, sequence)
type | VARCHAR
group | VARCHAR
date | TIMESTAMP
isok | BOOLEAN
With an index on (group,isok) where isok = false还有一个身长1000米的孩子：
Table child
Column | Type
------------+-------------------------------
parentid | BIGINT (Foreign Key)
field1 | VARCHAR
field2 | VARCHAR
With an index on (parentid)1个亲本可能有0~N个子女。
我需要执行以下查询：
SELECT p.id, p.type, p.date, c.field1, c.field2
FROM parent p
LEFT OUTER JOIN child AS c ON p.id = c.parentid
WHERE group = 'groupname' AND isok = false;解释分析告诉我，查询计划是：
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Right Join (cost=223072.57..34724441.40 rows=698363 width=65) (actual time=7944.249..933430.677 rows=286257 loops=1)
Hash Cond: (c.parentid = p.id)
-> Seq Scan on child c (cost=0.00..23840617.04 rows=1217573504 width=47) (actual time=0.005..488678.149 rows=1217573499 loops=1)
-> Hash (cost=220871.38..220871.38 rows=176095 width=26) (actual time=206.169..206.169 rows=283686 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 17731kB
-> Index Scan using parent_group_nok_idx on parent p (cost=0.55..220871.38 rows=176095 width=26) (actual time=0.032..115.183 rows=283686 loops=1)
Index Cond: (((group)::text = 'groupname'::text) AND (isok = false))
Total runtime: 933486.035 ms当我禁用When扫描时：
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=1.13..35309490.28 rows=698363 width=65) (actual time=0.684..42144.558 rows=286257 loops=1)
-> Index Scan using parent_group_nok_idx on parent p (cost=0.55..220871.38 rows=176095 width=26) (actual time=0.030..122.959 rows=283686 loops=1)
Index Cond: (((group)::text = 'groupname'::text) AND (isok = false))
-> Index Scan using child_parentid_idx on child c (cost=0.58..184.74 rows=1452 width=47) (actual time=0.145..0.147 rows=1 loops=283686)
Index Cond: (parentid = p.id)
Total runtime: 42200.478 ms我能做什么(除了禁用seq扫描)“强制”优化器选择索引方式？
Stack Overflow用户
回答已采纳
发布于 2016-02-25 09:09:31
在查找类似的问题之后，通过阅读本文：查询-计划器，我尝试使用CTE查询:下面是我使用的查询：
WITH cte AS (
SELECT id, type, date
FROM parent
WHERE group = 'groupname' AND isok = false
ORDER BY id ASC
)
SELECT cte.id, cte.type, cte.date, c.field1, c.field2
FROM cte LEFT OUTER JOIN child c ON c.parentid = cte.id;现在，有一个查询计划：
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=236655.35..33098268.28 rows=238777013 width=56) (actual time=243.160..1473.618 rows=286257 loops=1)
CTE cte
-> Sort (cost=236214.54..236654.77 rows=176095 width=26) (actual time=243.135..314.067 rows=283686 loops=1)
Sort Key: e.id
Sort Method: quicksort Memory: 34451kB
-> Index Scan using parent_group_nok_idx on parent (cost=0.55..220871.38 rows=176095 width=26) (actual time=0.041..113.058 rows=283686 loops=1)
Index Cond: (((group)::text = 'groupname'::text) AND (isok = false))
-> CTE Scan on cte (cost=0.00..3521.90 rows=176095 width=18) (actual time=243.140..449.385 rows=283686 loops=1)
-> Index Scan using child_parentid_idx on child c (cost=0.58..173.03 rows=1356 width=46) (actual time=0.002..0.003 rows=1 loops=283686)
Index Cond: (parentid = cte.id)
Total runtime: 1526.945 ms我的索引现在被使用了。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/35606254
复制相关文章
相似问题
腾讯云开发者
