PostgreSQL查询优化，阅读EXPLAIN

问题描述：

我正在阅读运行查询的解释输出。这是结果：https://explain.depesz.com/s/5EfR PostgreSQL查询优化，阅读EXPLAIN

我看到＃34行，数据库正在做一个非常昂贵的索引扫描，导致删除单个行。

我是否正确地读这个？另外，想法可能会导致这种情况？

查询：在此之前加入

explain analyze select *, posts.type as type, posts.created as created, posts.image_url as image_url, posts.id as post_id, posts.organization_id as id, 
    urls.image_url as url_image_url, 
    ts_headline('english',posts.text , to_tsquery('english', 'state') , $$StartSel='<span class="text-highlight">',StopSel=</span>, HighlightAll=true$$) as text, 
    ts_headline('english',posts.attachment_description, to_tsquery('english', 'state') , $$StartSel='<span class="text-highlight">',StopSel=</span>, HighlightAll=true$$) as attachment_description, 
    ts_headline('english',posts.attachment_title, to_tsquery('english', 'state') , $$StartSel='<span class="text-highlight">',StopSel=</span>, HighlightAll=true$$) as attachment_title 
    from vision2.posts15 as posts join vision2.organizations on organizations.id=posts.organization_id left join vision2.urls on urls.id = posts.url_id where chunks @@ to_tsquery('english', 'state') and string_to_array(upper(organizations.irs_state), '') && array['NJ'] and Date(posts.created) >= '2017-08-10' and Date(posts.created) <= '2017-08-24' and Date(posts.partition_date) >= '2017-08-10' and Date(posts.partition_date) <= '2017-08-24' order by posts.created desc offset 0 limit 40

注意，在各行很便宜（0.013ms），但有在该表870k循环。您是否尝试过在date_created和/或块上创建索引？ –

请注意，在规划的上几层上，行大小相当大（〜1K）。（并且哈希表可能会溢出到磁盘）。（顺便说一下：*请*编辑你的查询到可读的东西...） – wildplasser

你可以仔细检查你发布的查询是否符合执行计划吗？有些东西不匹配，即执行计划中没有partition_date筛选器，但它们位于where子句中，执行计划中的解析器正在查找educ，而查询显示（'english'，'state “）。无论如何，我的直觉是查询不与SELECT 4执行计划绑定。执行计划实际上看起来可能会有两次在帖子表上流氓加入。 – HodgePodge

答

试图限制数据。您可以使用CTE，因为它们已经实现了一次，并且如果您喜欢，可以像优化栅栏或临时表一样工作。

所以您的查询看起来是这样的：

WITH cte_posts AS (
    select type, created, image_url, id as post_id, organization_id as id, url_id, 
     ts_headline('english', 
        text, 
        to_tsquery('english', 'state'), 
        $$StartSel='<span class="text-highlight">',StopSel=</span>, HighlightAll=true$$) as text, 
     ts_headline('english', 
        attachment_description, 
        to_tsquery('english', 'state'), 
        $$StartSel='<span class="text-highlight">',StopSel=</span>, HighlightAll=true$$) as attachment_description, 
     ts_headline('english', 
        attachment_title, 
        to_tsquery('english', 'state'), 
        $$StartSel='<span class="text-highlight">',StopSel=</span>, HighlightAll=true$$) as attachment_title 
    from vision2.posts15 
    where Date(created) BETWEEN '2017-08-10' AND '2017-08-24' 
    and Date(partition_date) BETWEEN '2017-08-10' AND '2017-08-24' 
    AND chunks @@ to_tsquery('english', 'state') --is that column in posts15 table? 
) 
SELECT cte_posts.*, urls.image_url as url_image_url 
FROM cte_posts 
join vision2.organizations on organizations.id = cte_posts.id 
left join vision2.urls on urls.id = cte_posts.url_id 
     --you could try moving this WHERE to CTE as well, depending on your data 
where string_to_array(upper(organizations.irs_state), '') && array['NJ'] 
order by cte_posts.created desc 
offset 0 
limit 40

PostgreSQL查询优化，阅读EXPLAIN

相关推荐