Postgres index is not used

Question

I have an over 30s query that looks like this

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;

This is the index that is expected to be used (n.b.: "createdAt" type is timestamp with timezone)

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

and my_to_char function looks like

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;

and finally the explain (analyze, verbose, buffers) results

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

I've searched for the solution but nothing solves my issue, is there any option that I should try?

Answer 1

In general an index is only used if the expression in the where clause matches exactly the index expression. Plus leading columns of the index are preferred.

You do not use my_to_char("createdAt") in where clause but the expression `"createdAt"::date.

You would need to define an index on "createdAt"::date - which is a better choice anyway as that makes the index smaller. A date is stored in 4 bytes, the character representation takes up 10 bytes.

As you only reference "createdAt"::date in your query, that expression should also be the leading expression of an index. Even if you changed your unique index to use "createdAt"::date it would most probably not be used.

The following index should help:

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

As you have join conditions on deletedAt and type a partial index might even be better:

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

Edit

You forgot to mention that you are using a timestamp with time zone column which complicates things (which is a nice example why it's always a good idea to include the create table statements).

You can still work on date value, but you will need to change your function, query and index:

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

Then you can create this index:

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

and then use the following condition in your query:

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