postgresql - Postgres : Optimisation for query "WHERE id IN (...)"

Question

我有一个表(超过 200 万条记录)用于跟踪分类帐。有的条目加分，有的条目减分(只有两种条目)。减去点数的条目始终引用使用 referenceentryid 从中减去的(添加)条目。添加的条目在 referenceentryid 中总是有 NULL。

这个表有一个 dead 列，当一些添加被耗尽或过期时，或者当减法指向“dead”时，工作人员会将其设置为 true “补充。由于该表在 dead=false 上有部分索引，因此对事件行的 SELECT 工作得非常快。

我的问题是将 dead 设置为 NULL 的 worker 的性能。

流程是:1.每次添加都获取一个条目，指示添加、减去的数量以及是否过期。2. 过滤掉未过期且加法大于减法的词条。3. 在 id 或 referenceentryid 在已过滤条目集中的每一行上更新 dead=true。

WITH entries AS 
(
    SELECT 
        additions.id AS id,
        SUM(subtractions.amount) AS subtraction,
        additions.amount AS addition,
        additions.expirydate <= now() AS expired
    FROM 
        loyalty_ledger AS subtractions
    INNER JOIN 
        loyalty_ledger AS additions
    ON 
        additions.id = subtractions.referenceentryid
    WHERE
        subtractions.dead = FALSE
        AND subtractions.referenceentryid IS NOT NULL
    GROUP BY 
        subtractions.referenceentryid, additions.id
), dead_entries AS (
    SELECT
        id
    FROM
        entries
    WHERE
        subtraction >= addition OR expired = TRUE
)
-- THE SLOW BIT:
SELECT
    *
FROM 
    loyalty_ledger AS ledger
WHERE
    ledger.dead = FALSE AND
    (ledger.id IN (SELECT id FROM dead_entries) OR ledger.referenceentryid IN (SELECT id FROM dead_entries));

在上面的查询中，内部部分运行得非常快(几秒钟)，而最后一部分将永远运行。

我在表上有以下索引:

CREATE TABLE IF NOT EXISTS loyalty_ledger (
        id SERIAL PRIMARY KEY,
        programid bigint NOT NULL,   
        FOREIGN KEY (programid) REFERENCES loyalty_programs(id) ON DELETE CASCADE,
        referenceentryid    bigint,
        FOREIGN KEY (referenceentryid) REFERENCES loyalty_ledger(id) ON DELETE CASCADE,
        customerprofileid bigint NOT NULL,
        FOREIGN KEY (customerprofileid) REFERENCES customer_profiles(id) ON DELETE CASCADE,
        amount int NOT NULL,
        expirydate TIMESTAMPTZ,
        dead boolean DEFAULT false,
        expired boolean DEFAULT false
);

CREATE index loyalty_ledger_referenceentryid_idx ON loyalty_ledger (referenceprofileid) WHERE dead = false;
CREATE index loyalty_ledger_customer_program_idx ON loyalty_ledger (customerprofileid, programid) WHERE dead = false;

我正在尝试优化查询的最后一部分。EXPLAIN 给出以下内容:

"Index Scan using loyalty_ledger_referenceentryid_idx on loyalty_ledger ledger  (cost=103412.24..4976040812.22 rows=986583 width=67)"
"  Filter: ((SubPlan 3) OR (SubPlan 4))"
"  CTE entries"
"    ->  GroupAggregate  (cost=1.47..97737.83 rows=252177 width=25)"
"          Group Key: subtractions.referenceentryid, additions.id"
"          ->  Merge Join  (cost=1.47..91390.72 rows=341928 width=28)"
"                Merge Cond: (subtractions.referenceentryid = additions.id)"
"                ->  Index Scan using loyalty_ledger_referenceentryid_idx on loyalty_ledger subtractions  (cost=0.43..22392.56 rows=341928 width=12)"
"                      Index Cond: (referenceentryid IS NOT NULL)"
"                ->  Index Scan using loyalty_ledger_pkey on loyalty_ledger additions  (cost=0.43..80251.72 rows=1683086 width=16)"
"  CTE dead_entries"
"    ->  CTE Scan on entries  (cost=0.00..5673.98 rows=168118 width=4)"
"          Filter: ((subtraction >= addition) OR expired)"
"  SubPlan 3"
"    ->  CTE Scan on dead_entries  (cost=0.00..3362.36 rows=168118 width=4)"
"  SubPlan 4"
"    ->  CTE Scan on dead_entries dead_entries_1  (cost=0.00..3362.36 rows=168118 width=4)"

我查询的最后一部分似乎效率很低。关于如何加快速度的任何想法？

Answer 1

对于大型数据集，我发现半连接比查询列表具有更好的性能:

from
  loyalty_ledger as ledger
WHERE
    ledger.dead = FALSE AND (
    exists (
      select null
      from dead_entries d
      where d.id = ledger.id
      ) or
    exists (
      select null
      from dead_entries d
      where d.id = ledger.referenceentryid
      )
    )

老实说，我不知道，但我认为每一个都值得一试。它的代码更少，更直观，但不能保证它们会更好地工作:

ledger.dead = FALSE AND
exists (
  select null
  from dead_entries d
  where d.id = ledger.id or d.id = ledger.referenceentryid 
)

或

ledger.dead = FALSE AND
exists (
  select null
  from dead_entries d
  where d.id in (ledger.id, ledger.referenceentryid) 
)