explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fAVN

Settings
# exclusive inclusive rows x rows loops node
1. 2.402 877.991 ↑ 14.8 1,830 1

Sort (cost=11,292.25..11,360.06 rows=27,126 width=96) (actual time=877.914..877.991 rows=1,830 loops=1)

  • Sort Key: (CASE WHEN ((t.tx_date >= '2018-01-01'::date) AND (t.tx_date <= '2018-12-31'::date)) THEN 'previousPeriod'::text WHEN ((t.tx_date >= '2019-01-01'::date) AND (t.tx_date <= '2019-12-31'::date)) THEN 'currentPeriod'::text ELSE NULL::text END), (sum((t.home_amount / '10000000'::numeric)))
  • Sort Method: quicksort Memory: 191kB
  • NULL)
2. 101.705 875.589 ↑ 14.8 1,830 1

GroupAggregate (cost=8,190.33..9,294.77 rows=27,126 width=96) (actual time=711.326..875.589 rows=1,830 loops=1)

  • Group Key: ((t.customer_id)::character varying), (CASE WHEN ((t.tx_date >= '2018-01-01'::date) AND (t.tx_date <= '2018-12-31'::date)) THEN 'previousPeriod'::text WHEN ((t.tx_date >= '2019-01-01'::date) AND (t.tx_date <= '2019-12-31'::date)) THEN 'currentPeriod'::text ELSE NULL::text END)
3. 235.970 773.884 ↓ 12.7 362,816 1

Sort (cost=8,190.33..8,262.03 rows=28,678 width=70) (actual time=711.308..773.884 rows=362,816 loops=1)

  • Sort Key: ((t.customer_id)::character varying), (CASE WHEN ((t.tx_date >= '2018-01-01'::date) AND (t.tx_date <= '2018-12-31'::date)) THEN 'previousPeriod'::text WHEN ((t.tx_date >= '2019-01-01'::date) AND (t.tx_date <= '2019-12-31'::date)) THEN 'currentPeriod'::text ELSE NULL::text END)
  • Sort Method: external merge Disk: 11,984kB
4. 69.533 537.914 ↓ 12.7 362,816 1

Nested Loop (cost=1.26..6,067.06 rows=28,678 width=70) (actual time=0.066..537.914 rows=362,816 loops=1)

5. 0.507 0.507 ↓ 3.3 99 1

Index Scan using accounts_pk on accounts a (cost=0.56..235.72 rows=30 width=13) (actual time=0.040..0.507 rows=99 loops=1)

  • Index Cond: (company_id = 178,348,139.000000000)
  • Filter: (account_type_id = '10'::numeric)
  • Rows Removed by Filter: 283
6. 24.552 467.874 ↓ 23.6 3,665 99

Append (cost=0.70..178.49 rows=155 width=28) (actual time=0.017..4.726 rows=3,665 loops=99)

7. 443.322 443.322 ↓ 23.6 3,665 99

Index Scan using txdetails_p6_account_id_company_id_tx_date_idx on txdetails_p6 t (cost=0.70..177.71 rows=155 width=28) (actual time=0.017..4.478 rows=3,665 loops=99)

  • Index Cond: ((account_id = a.account_id) AND (company_id = 178,348,139.000000000) AND (tx_date >= '2018-01-01'::date) AND (tx_date <= '2019-12-31'::date))
  • Filter: (CASE WHEN ((tx_date >= '2018-01-01'::date) AND (tx_date <= '2018-12-31'::date)) THEN 'previousPeriod'::text WHEN ((tx_date >= '2019-01-01'::date) AND (tx_date <= '2019-12-31'::date)) THEN 'currentPeriod'::text ELSE NULL::text END IS NOT
Planning time : 2.827 ms
Execution time : 879.905 ms