PostgreSQL：存在与左联接 [英] PostgreSQL: exists vs left join

问题描述

我多次听到postgres处理 exists 的查询要比 left join 更快。
http://archives.postgresql.org/pgsql- performance / 2002-12 / msg00185.php

I heard many times that postgres handles exists queries even faster then left join. http://archives.postgresql.org/pgsql-performance/2002-12/msg00185.php

对于一个表聚合绝对是正确的。

That's definitely true for one table aggregation.

但在我们的例子中，它们不止一个，并且使用 exists 构建相同的查询，使postgres永远挂起：

But in our case their is more then one and the same query build with exists that make postgres to hang forever:

explain 
SELECT count(DISTINCT "groups".id) AS count_all 
FROM "groups"
WHERE (exists(
    select * from products p where groups.id = p.group_id AND exists(
        select * from products_categories pc where p.id = pc.product_id AND pc.category_id in (2,3))) AND groups.id != 3) 

结果：

 Aggregate  (cost=26413436.66..26413436.67 rows=1 width=4)
   ->  Seq Scan on groups  (cost=0.00..26413403.84 rows=13126 width=4)
         Filter: ((id <> 3) AND (subplan))
         SubPlan
           ->  Index Scan using index_products_on_group_id on products p  (cost=0.00..1006.13 rows=1 width=1483)
                 Index Cond: ($1 = group_id)
                 Filter: (subplan)
                 SubPlan
                   ->  Seq Scan on products_categories pc  (cost=0.00..498.49 rows=1 width=8)
                         Filter: ((category_id = ANY ('{2,3}'::integer[])) AND ($0 = product_id))

这是执行时间过长的根本原因吗？
是某种配置问题吗？

That is the root cause of incredibly long execution time? Is that some kind of configuration problem?

谢谢，
Bogdan。

Thanks, Bogdan.

推荐答案

好吧，对于组中的每一行，postgresql都会对products_categories进行全面扫描，这是不好的。

Well, for each row in "groups", postgresql is doing a full scan of products_categories, which isn't good. Not necessarily a configuration problem, but perhaps the query could be stated without nesting subqueries like that?

SELECT count(DISTINCT "groups".id) AS count_all 
FROM "groups"
WHERE exists(
    select 1 from products p where groups.id = p.group_id
             join products_categories pc on pc.product_id = p.id
    where pc.category_id in (2,3)
    ) and groups.id <> 3

products_categories 的索引也 product_id ？

这篇关于PostgreSQL：存在与左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！