explain.depesz.com

PostgreSQL's explain analyze made readable

Result: I8Bv

Settings
# exclusive inclusive rows x rows loops node
1. 0.041 10.257 ↑ 1,396.0 1 1

GroupAggregate (cost=94,913.01..94,977.57 rows=1,396 width=153) (actual time=10.256..10.257 rows=1 loops=1)

  • Group Key: mc.id, t_1.account_id, ac.account_name
2. 0.057 10.216 ↑ 35.8 39 1

Sort (cost=94,913.01..94,916.50 rows=1,396 width=111) (actual time=10.201..10.216 rows=39 loops=1)

  • Sort Key: mc.id, t_1.account_id, ac.account_name
  • Sort Method: quicksort Memory: 30kB
3. 0.035 10.159 ↑ 35.8 39 1

Nested Loop Left Join (cost=68,870.57..94,840.09 rows=1,396 width=111) (actual time=9.582..10.159 rows=39 loops=1)

4. 0.068 10.085 ↑ 35.8 39 1

Nested Loop Left Join (cost=68,870.13..89,218.50 rows=1,396 width=105) (actual time=9.576..10.085 rows=39 loops=1)

5. 0.058 9.939 ↑ 35.8 39 1

Nested Loop (cost=68,869.70..78,950.35 rows=1,396 width=99) (actual time=9.566..9.939 rows=39 loops=1)

6. 0.053 9.764 ↑ 35.8 39 1

Hash Join (cost=68,869.28..69,310.89 rows=1,396 width=73) (actual time=9.528..9.764 rows=39 loops=1)

  • Hash Cond: (t_1.cid = mc.id)
7. 0.000 7.488 ↑ 77.7 39 1

Finalize GroupAggregate (cost=68,730.17..69,133.50 rows=3,031 width=54) (actual time=7.290..7.488 rows=39 loops=1)

  • Group Key: t_1.id
  • Filter: ((t_1.transaction_amount + sum(CASE WHEN (a_1.charge_transaction_id = t_1.id) THEN (a_1.allocation_amount * '-1'::numeric) WHEN (a_1.credit_transaction_id = t_1.id) THEN a_1.allocation_amount ELSE '0'::numeric END)) <> '0'::numeric)
8. 7.914 8.964 ↑ 64.8 39 1

Gather Merge (cost=68,730.17..69,056.46 rows=2,526 width=54) (actual time=7.273..8.964 rows=39 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 0.075 1.050 ↑ 97.2 13 3

Partial GroupAggregate (cost=67,730.14..67,764.87 rows=1,263 width=54) (actual time=0.323..0.350 rows=13 loops=3)

  • Group Key: t_1.id
10. 0.144 0.975 ↑ 97.2 13 3

Sort (cost=67,730.14..67,733.30 rows=1,263 width=36) (actual time=0.319..0.325 rows=13 loops=3)

  • Sort Key: t_1.id
  • Sort Method: quicksort Memory: 28kB
11. 0.078 0.831 ↑ 97.2 13 3

Nested Loop Left Join (cost=180.73..67,665.08 rows=1,263 width=36) (actual time=0.098..0.277 rows=13 loops=3)

12. 0.205 0.402 ↑ 97.2 13 3

Parallel Bitmap Heap Scan on transactions t_1 (cost=173.39..33,215.55 rows=1,263 width=22) (actual time=0.087..0.134 rows=13 loops=3)

  • Recheck Cond: (cid = 13123)
  • Filter: (date_trunc('day'::text, created_on) <= '2019-05-08 00:00:00-06'::timestamp with time zone)
  • Heap Blocks: exact=38
13. 0.197 0.197 ↑ 233.2 39 1

Bitmap Index Scan on idx_transactions_cid (cost=0.00..172.63 rows=9,093 width=0) (actual time=0.196..0.197 rows=39 loops=1)

  • Index Cond: (cid = 13123)
14. 0.039 0.351 ↓ 0.0 0 39

Bitmap Heap Scan on allocations a_1 (cost=7.33..27.25 rows=3 width=14) (actual time=0.009..0.009 rows=0 loops=39)

  • Recheck Cond: ((charge_transaction_id = t_1.id) OR (credit_transaction_id = t_1.id))
  • Filter: (date_trunc('day'::text, created_on) <= '2019-05-08 00:00:00-06'::timestamp with time zone)
15. 0.078 0.312 ↓ 0.0 0 39

BitmapOr (cost=7.33..7.33 rows=8 width=0) (actual time=0.008..0.008 rows=0 loops=39)

16. 0.117 0.117 ↓ 0.0 0 39

Bitmap Index Scan on idx_allocations_charge_transaction_id (cost=0.00..3.63 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=39)

  • Index Cond: (charge_transaction_id = t_1.id)
17. 0.117 0.117 ↓ 0.0 0 39

Bitmap Index Scan on idx_allocations_credit_transaction_id (cost=0.00..3.70 rows=6 width=0) (actual time=0.003..0.003 rows=0 loops=39)

  • Index Cond: (credit_transaction_id = t_1.id)
18. 0.888 2.223 ↑ 1.0 1,843 1

Hash (cost=116.06..116.06 rows=1,844 width=27) (actual time=2.222..2.223 rows=1,843 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 125kB
19. 1.335 1.335 ↑ 1.0 1,843 1

Seq Scan on clients mc (cost=0.00..116.06 rows=1,844 width=27) (actual time=0.008..1.335 rows=1,843 loops=1)

  • Filter: (company_status_type_id = ANY ('{4,5}'::integer[]))
  • Rows Removed by Filter: 2165
20. 0.117 0.117 ↑ 1.0 1 39

Index Scan using pk_accounts on accounts ac (cost=0.42..6.91 rows=1 width=30) (actual time=0.003..0.003 rows=1 loops=39)

  • Index Cond: (id = t_1.account_id)
21. 0.078 0.078 ↓ 0.0 0 39

Index Scan using idx_allocations_charge_transaction_id on allocations a (cost=0.43..7.35 rows=1 width=14) (actual time=0.002..0.002 rows=0 loops=39)

  • Index Cond: (charge_transaction_id = t_1.id)
  • Filter: ((created_on >= '2019-05-08 00:00:00-06'::timestamp with time zone) AND (created_on <= '2019-06-07 00:00:00-06'::timestamp with time zone))
22. 0.039 0.039 ↓ 0.0 0 39

Index Scan using pk_transactions on transactions t (cost=0.44..4.03 rows=1 width=14) (actual time=0.001..0.001 rows=0 loops=39)

  • Index Cond: (id = a.credit_transaction_id)
  • Filter: ((created_on >= '2019-05-08 00:00:00-06'::timestamp with time zone) AND (created_on <= '2019-06-07 00:00:00-06'::timestamp with time zone))
Planning time : 1.130 ms