postgresql - 不使用 Postgres 索引

Question

我有一个 30 多岁的查询，看起来像这样

select 
     coalesce(sum("paidInstallment"-(plafond/tenor*frequency)),0) 
from 
    account_transaction_debit atd 
    join r_account_transaction_debit ratd on atd.id = ratd."accountTransactionDebitId" and atd."deletedAt" isnull and ratd."deletedAt" isnulland atd."type" = 'INSTALLMENT'
    join account a on a.id = ratd."accountId" and a."deletedAt" isnull and a.id = 4735
    join r_account_transaction_debit_installment ratdi ratd."accountTransactionDebitId" =  atd.id and ratdi."deletedAt" isnull
    join installment i on i.id = ratdi."installmentId" and i."deletedAt" isnull
    join r_loan_installment rli on i.id = rli."installmentId" rli."deletedAt" isnull
    join loan l on l.id = rli."loanId" and l."deletedAt" isnull
where 
   atd."createdAt"::date between date_trunc('week',current_date) and current_date;

这是预期要使用的索引(注意:“createdAt”类型是带时区的时间戳)

CREATE UNIQUE INDEX 
account_transaction_debit_deletedAt_type_createdAtDate_id_idx 
ON account_transaction_debit ("deletedAt","type",my_to_char("createdAt"),id);

和my_to_char函数看起来像

CREATE OR REPLACE FUNCTION my_to_char(some_time timestamptz) 
  RETURNS text
AS
$BODY$
    select to_char($1, 'yyyy-mm-dd');
$BODY$
LANGUAGE sql
IMMUTABLE;

最后是解释(分析、详细、缓冲)结果

Aggregate  (cost=17943983.42..17943983.43 rows=1 width=32) (actual time=3020772.135..3020772.135 rows=1 loops=1)
  Output: COALESCE(sum((i."paidInstallment" - ((l.plafond / (l.tenor)::numeric) * (i.frequency)::numeric))), '0'::numeric)
  Buffers: shared hit=84541, temp read=25220 written=25092
  ->  Nested Loop  (cost=157262.23..8369328.64 rows=638310318 width=18) (actual time=3520.565..593472.501 rows=3168056628 loops=1)
        Output: i."paidInstallment", l.plafond, l.tenor, i.frequency
        Buffers: shared hit=84541, temp read=25220 written=25092
        ->  Nested Loop  (cost=125695.82..280946.58 rows=1384621 width=22) (actual time=1266.622..6543.090 rows=1496484 loops=1)
              Output: i."paidInstallment", i.frequency, l.plafond, l.tenor, a.id
              Buffers: shared hit=53058, temp read=25012 written=24886
              ->  Index Scan using account_id_idx on public.account a  (cost=0.29..8.31 rows=1 width=4) (actual time=0.012..0.518 rows=1 loops=1)
                    Output: a.id, a."totalDebit", a."totalCredit", a."totalBalance", a."createdAt", a."updatedAt", a."deletedAt", a."oldId", a.threshold
                    Index Cond: (a.id = 4735)
                    Filter: (a."deletedAt" IS NULL)
                    Buffers: shared hit=93
              ->  Hash Join  (cost=125695.53..267092.07 rows=1384621 width=18) (actual time=1266.608..6240.859 rows=1496484 loops=1)
                    Output: i."paidInstallment", i.frequency, l.plafond, l.tenor
                    Hash Cond: (rli."loanId" = l.id)
                    Buffers: shared hit=52965, temp read=25012 written=24886
                    ->  Hash Join  (cost=120652.07..228476.25 rows=1434437 width=14) (actual time=1224.901..4429.943 rows=1496484 loops=1)
                          Output: i."paidInstallment", i.frequency, rli."loanId"
                          Hash Cond: (ratdi."installmentId" = i.id)
                          Buffers: shared hit=49833, temp read=21586 written=21462
                          ->  Hash Join  (cost=52321.11..118507.38 rows=1493212 width=12) (actual time=552.083..1704.683 rows=1496484 loops=1)
                                Output: ratdi."installmentId", rli."installmentId", rli."loanId"
                                Hash Cond: (ratdi."installmentId" = rli."installmentId")
                                Buffers: shared hit=21624, temp read=9403 written=9341
                                ->  Seq Scan on public.r_account_transaction_debit_installment ratdi  (cost=0.00..25809.68 rows=1511094 width=4) (actual time=0.010..303.506 rows=1496491 loops=1)
                                      Output: ratdi."installmentId"
                                      Filter: (ratdi."deletedAt" IS NULL)
                                      Rows Removed by Filter: 8502
                                      Buffers: shared hit=10606
                                ->  Hash  (cost=26774.97..26774.97 rows=1557051 width=8) (actual time=551.360..551.360 rows=1529141 loops=1)
                                      Output: rli."installmentId", rli."loanId"
                                      Buckets: 131072  Batches: 32  Memory Usage: 2897kB
                                      Buffers: shared hit=11018, temp written=5047
                                      ->  Seq Scan on public.r_loan_installment rli  (cost=0.00..26774.97 rows=1557051 width=8) (actual time=0.010..267.452 rows=1529141 loops=1)
                                            Output: rli."installmentId", rli."loanId"
                                            Filter: (rli."deletedAt" IS NULL)
                                            Rows Removed by Filter: 17031
                                            Buffers: shared hit=11018
                          ->  Hash  (cost=43020.87..43020.87 rows=1456007 width=14) (actual time=672.617..672.617 rows=1510902 loops=1)
                                Output: i."paidInstallment", i.frequency, i.id
                                Buckets: 131072  Batches: 32  Memory Usage: 3244kB
                                Buffers: shared hit=28209, temp written=6413
                                ->  Seq Scan on public.installment i  (cost=0.00..43020.87 rows=1456007 width=14) (actual time=0.010..360.023 rows=1510902 loops=1)
                                      Output: i."paidInstallment", i.frequency, i.id
                                      Filter: (i."deletedAt" IS NULL)
                                      Rows Removed by Filter: 24993
                                      Buffers: shared hit=28209
                    ->  Hash  (cost=3845.53..3845.53 rows=68875 width=12) (actual time=41.500..41.500 rows=69253 loops=1)
                          Output: l.plafond, l.tenor, l.id
                          Buckets: 131072  Batches: 2  Memory Usage: 2655kB
                          Buffers: shared hit=3132, temp written=151
                          ->  Seq Scan on public.loan l  (cost=0.00..3845.53 rows=68875 width=12) (actual time=0.005..25.918 rows=69253 loops=1)
                                Output: l.plafond, l.tenor, l.id
                                Filter: (l."deletedAt" IS NULL)
                                Rows Removed by Filter: 2611
                                Buffers: shared hit=3132
        ->  Materialize  (cost=31566.41..109504.70 rows=461 width=4) (actual time=0.002..0.130 rows=2117 loops=1496484)
              Output: ratd."accountId"
              Buffers: shared hit=31483, temp read=208 written=206
              ->  Hash Join  (cost=31566.41..109502.39 rows=461 width=4) (actual time=2253.937..2295.318 rows=2117 loops=1)
                    Output: ratd."accountId"
                    Hash Cond: ((atd.id)::double precision = ratd."accountTransactionDebitId")
                    Buffers: shared hit=31483, temp read=208 written=206
                    ->  Seq Scan on public.account_transaction_debit atd  (cost=0.00..77463.04 rows=7467 width=4) (actual time=2048.273..2078.574 rows=20319 loops=1)
                          Output: atd.id
                          Filter: ((atd."deletedAt" IS NULL) AND (atd.type = 'INSTALLMENT'::text) AND ((atd."createdAt")::date <= ('now'::cstring)::date) AND ((atd."createdAt")::date >= date_trunc('week'::text, (('now'::cstring)::date)::timestamp with time zone)))
                          Rows Removed by Filter: 1496536
                          Buffers: shared hit=20688
                    ->  Hash  (cost=30009.74..30009.74 rows=94854 width=8) (actual time=205.009..205.009 rows=102568 loops=1)
                          Output: ratd."accountTransactionDebitId", ratd."accountId"
                          Buckets: 131072  Batches: 2  Memory Usage: 3021kB
                          Buffers: shared hit=10795, temp written=175
                          ->  Seq Scan on public.r_account_transaction_debit ratd  (cost=0.00..30009.74 rows=94854 width=8) (actual time=17.923..180.127 rows=102568 loops=1)
                                Output: ratd."accountTransactionDebitId", ratd."accountId"
                                Filter: ((ratd."deletedAt" IS NULL) AND (ratd."accountId" = 4735))
                                Rows Removed by Filter: 1414286
                                Buffers: shared hit=10795
Planning time: 1.181 ms
Execution time: 3020772.395 ms

我已经搜索了解决方案，但没有解决我的问题，有没有我应该尝试的选项？

Answer 1

通常只有当 where 子句中的表达式完全匹配索引表达式时才使用索引。加上索引的前导列是首选。

您不要在 where 子句中使用 my_to_char("createdAt")，而是使用表达式 `"createdAt"::date。

您需要在 "createdAt"::date 上定义一个索引——无论如何这是一个更好的选择，因为这会使索引更小。一个date存储在4个字节中，字符表示占用10个字节。

由于您在查询中仅引用"createdAt"::date，因此该表达式也应该是索引的前导 表达式。即使您将唯一索引更改为使用 "createdAt"::date，它也很可能不会被使用。

以下索引应该有所帮助:

CREATE INDEX ON account_transaction_debit ( ("createdAt"::date) );

因为您在 deletedAt 和 type 上有连接条件，部分索引可能会更好:

CREATE INDEX ON account_transaction_debit ( ("createdAt"::date), type )
where "deletedAt" is null;

编辑

您忘记提及您使用的是 timestamp with time zone 列，这使事情变得复杂(这是一个很好的例子，为什么包含 create table 陈述)。

您仍然可以处理日期值，但您需要更改函数、查询和索引:

CREATE OR REPLACE FUNCTION get_date(some_time timestamptz) 
  RETURNS date
AS
$BODY$
    select some_time::date
$BODY$
LANGUAGE sql
IMMUTABLE;

然后你可以创建这个索引:

CREATE INDEX ON account_transaction_debit ( (get_date("createdAt")), type )
where "deletedAt" is null;

然后在您的查询中使用以下条件:

where get_date(atd."createdAt") between date_trunc('week',current_date) and current_date;