PostgreSQL慢JOIN与CASE语句

Question

PostgreSQL慢JOIN与CASE语句

sqlpostgresqljoincasepostgresql-performance

6

在我的数据库中，我有一张包含了大约3500条记录的表，作为更复杂查询的一部分，我尝试使用“CASE”条件对其进行自身连接，就像下面展示的那样。

SELECT *
FROM some_table AS t1
JOIN some_table AS t2 ON t1.type = t2.type
    AND CASE
       WHEN t1.type = 'ab' THEN t1.first = t2.first
       WHEN t1.type = 'cd' THEN t1.second = t2.second
       -- Column type contains only one of 2 possible varchar values
    END;

问题在于这个查询需要3.2至4.5秒的时间来执行，而下一个请求只需要40至50毫秒。

SELECT *
FROM some_table AS t1
JOIN some_table AS t2 ON t1.type = t2.type
    AND (t1.first = t2.first OR t1.second = t2.second)

根据第一种情况中的执行计划，数据库处理了大约580万条记录，而表中只有大约3500条记录。该表上有以下索引：(id)、(type)、(type，first)和(type，second)。

我们正在使用的版本是： PostgreSQL 9.4.5 x86_64-unknown-linux-gnu版，由gcc (GCC) 4.4.7 20120 313 (Red Hat 4.4.7-16)编译，64位。

为什么PostgreSQL在这种情况下表现得如此奇怪？您有什么想法吗？

- legacy

2

请编辑您的问题，添加有关问题中表格（包括所有索引）的“create table”语句和使用“explain（analyze，verbose）”生成的执行计划。请使用格式化文本，不要使用屏幕截图。 - user330315

那是一个case表达式，而不是case语句。(一般建议，在where子句中使用AND/OR代替case表达式。) - jarlh

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Clodoaldo Neto · Accepted Answer

测试这个:

select *
from
    some_table as t1
    join
    some_table as t2 on
        t1.type = t2.type
        and
        (
            t1.type = 'ab' and t1.first = t2.first
            or
            t1.type = 'cd' and t1.second = t2.second
        )

为了获得更好的性能，根据函数创建索引：

create or replace function f (_type text, _first int, _second int)
returns integer as $$
    select case _type when 'ab' then _first else _second end;
$$ language sql immutable;

create index i on some_table(f(type, first, second));

在查询中使用该索引：

select *
from
    some_table as t1
    join
    some_table as t2 on
        t1.type = t2.type
        and
        f(t1.type, t1.first, t1.second) = f(t1.type, t2.first, t2.second)