本文介绍了PostgreSQL:存在与左联接的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我多次听到postgres处理 exists 的查询要比 left join 更快。

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:存在与左联接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-13 12:17