explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DBiJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.079 4,250.048 ↑ 2,012.9 128 1

Sort (cost=1,602,341.61..1,602,985.75 rows=257,655 width=96) (actual time=4,250.042..4,250.048 rows=128 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: 35kB
2. 0.056 4,249.969 ↑ 2,012.9 128 1

Finalize GroupAggregate (cost=1,524,935.25..1,571,446.01 rows=257,655 width=96) (actual time=4,150.099..4,249.969 rows=128 loops=1)

  • Group Key: ((t.item_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. 35.733 4,249.913 ↑ 1,705.1 159 1

Gather Merge (cost=1,524,935.25..1,561,649.46 rows=271,104 width=96) (actual time=4,150.093..4,249.913 rows=159 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 43.670 4,214.180 ↑ 2,557.6 53 3 / 3

Partial GroupAggregate (cost=1,523,935.23..1,529,357.31 rows=135,552 width=96) (actual time=4,145.219..4,214.180 rows=53 loops=3)

  • Group Key: ((t.item_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)
5. 68.168 4,170.510 ↑ 1.0 133,116 3 / 3

Sort (cost=1,523,935.23..1,524,274.11 rows=135,552 width=70) (actual time=4,145.194..4,170.510 rows=133,116 loops=3)

  • Sort Key: ((t.item_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: 4528kB
  • Worker 0: Sort Method: external merge Disk: 4408kB
  • Worker 1: Sort Method: external merge Disk: 4224kB
6. 250.134 4,102.342 ↑ 1.0 133,116 3 / 3

Hash Join (cost=346.73..1,509,122.99 rows=135,552 width=70) (actual time=14.890..4,102.342 rows=133,116 loops=3)

  • Hash Cond: (t.account_id = a.account_id)
7. 143.568 3,851.554 ↓ 1.9 2,048,338 3 / 3

Parallel Append (cost=0.70..1,503,887.45 rows=1,073,563 width=28) (actual time=0.106..3,851.554 rows=2,048,338 loops=3)

8. 3,707.986 3,707.986 ↓ 1.9 2,048,338 3 / 3

Parallel Index Only Scan using txdetails_p0_company_id_date_tx_date_is_no_post_tx_id_seque_idx on txdetails_p0 t (cost=0.70..1,498,519.64 rows=1,073,563 width=28) (actual time=0.106..3,707.986 rows=2,048,338 loops=3)

  • Index Cond: ((company_id = '79451168'::numeric) 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 NULL)
  • Heap Fetches: 6145013
9. 0.014 0.654 ↓ 1.0 45 3 / 3

Hash (cost=345.48..345.48 rows=44 width=13) (actual time=0.654..0.654 rows=45 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
10. 0.640 0.640 ↓ 1.0 45 3 / 3

Index Scan using accounts_pk on accounts a (cost=0.56..345.48 rows=44 width=13) (actual time=0.097..0.640 rows=45 loops=3)

  • Index Cond: (company_id = '79451168'::numeric)
  • Filter: (account_type_id = '10'::numeric)
  • Rows Removed by Filter: 213
Planning time : 6.125 ms
Execution time : 4,251.987 ms