explain.depesz.com

PostgreSQL's explain analyze made readable

Result: v7gq

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↑ 4.6 48 1

Sort (cost=59,153.09..59,153.64 rows=222 width=80) (actual rows=48 loops=1)

  • Output: customer.id, customer.name, ledger_entry.currency, billing_account.account_group, (sum(CASE WHEN ledger_entry.is_credit THEN ledger_entry.value ELSE ('-1'::numeric * ledger_entry.value) END))
  • Sort Key: customer.name, ledger_entry.currency, billing_account.account_group
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=21346
2. 0.000 0.000 ↑ 4.6 48 1

HashAggregate (cost=59,140.00..59,142.22 rows=222 width=80) (actual rows=48 loops=1)

  • Output: customer.id, customer.name, ledger_entry.currency, billing_account.account_group, (sum(CASE WHEN ledger_entry.is_credit THEN ledger_entry.value ELSE ('-1'::numeric * ledger_entry.value) END))
  • Group Key: customer.id, customer.name, ledger_entry.currency, billing_account.account_group, (sum(CASE WHEN ledger_entry.is_credit THEN ledger_entry.value ELSE ('-1'::numeric * ledger_entry.value) END))
  • Buffers: shared hit=21346
3. 0.000 0.000 ↑ 4.6 48 1

Append (cost=29,629.67..59,137.22 rows=222 width=80) (actual rows=48 loops=1)

  • Buffers: shared hit=21346
4. 0.000 0.000 ↑ 5.1 35 1

Finalize GroupAggregate (cost=29,629.67..29,652.87 rows=178 width=61) (actual rows=35 loops=1)

  • Output: customer.id, customer.name, ledger_entry.currency, billing_account.account_group, sum(CASE WHEN ledger_entry.is_credit THEN ledger_entry.value ELSE ('-1'::numeric * ledger_entry.value) END)
  • Group Key: customer.id, ledger_entry.currency, billing_account.account_group
  • Buffers: shared hit=11010
5. 0.000 0.000 ↑ 3.0 50 1

Gather Merge (cost=29,629.67..29,648.79 rows=148 width=61) (actual rows=50 loops=1)

  • Output: customer.id, ledger_entry.currency, billing_account.account_group, customer.name, (PARTIAL sum(CASE WHEN ledger_entry.is_credit THEN ledger_entry.value ELSE ('-1'::numeric * ledger_entry.value) END))
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=21225
6. 0.000 0.000 ↑ 4.4 17 3 / 3

Partial GroupAggregate (cost=28,629.65..28,631.69 rows=74 width=61) (actual rows=17 loops=3)

  • Output: customer.id, ledger_entry.currency, billing_account.account_group, customer.name, PARTIAL sum(CASE WHEN ledger_entry.is_credit THEN ledger_entry.value ELSE ('-1'::numeric * ledger_entry.value) END)
  • Group Key: customer.id, ledger_entry.currency, billing_account.account_group
  • Buffers: shared hit=21225
  • Worker 0: actual rows=2 loops=1
  • Buffers: shared hit=5143
  • Worker 1: actual rows=13 loops=1
  • Buffers: shared hit=5072
7. 0.000 0.000 ↓ 12.1 898 3 / 3

Sort (cost=28,629.65..28,629.84 rows=74 width=35) (actual rows=898 loops=3)

  • Output: customer.id, ledger_entry.currency, billing_account.account_group, customer.name, ledger_entry.is_credit, ledger_entry.value
  • Sort Key: customer.id, ledger_entry.currency, billing_account.account_group
  • Sort Method: quicksort Memory: 299kB
  • Buffers: shared hit=21225
  • Worker 0: actual rows=65 loops=1
  • Buffers: shared hit=5143
  • Worker 1: actual rows=144 loops=1
  • Buffers: shared hit=5072
8. 0.000 0.000 ↓ 12.1 898 3 / 3

Hash Join (cost=562.38..28,627.35 rows=74 width=35) (actual rows=898 loops=3)

  • Output: customer.id, ledger_entry.currency, billing_account.account_group, customer.name, ledger_entry.is_credit, ledger_entry.value
  • Inner Unique: true
  • Hash Cond: (billing_account.customer_id = customer.id)
  • Buffers: shared hit=21201
  • Worker 0: actual rows=65 loops=1
  • Buffers: shared hit=5131
  • Worker 1: actual rows=144 loops=1
  • Buffers: shared hit=5060
9. 0.000 0.000 ↓ 12.1 898 3 / 3

Hash Join (cost=555.90..28,620.67 rows=74 width=22) (actual rows=898 loops=3)

  • Output: ledger_entry.currency, ledger_entry.is_credit, ledger_entry.value, billing_account.account_group, billing_account.customer_id
  • Inner Unique: true
  • Hash Cond: (ledger_entry.account_id = billing_account.id)
  • Buffers: shared hit=21176
  • Worker 0: actual rows=65 loops=1
  • Buffers: shared hit=5120
  • Worker 1: actual rows=144 loops=1
  • Buffers: shared hit=5049
10. 0.000 0.000 ↓ 1.1 3,914 3 / 3

Parallel Seq Scan on public.ledger_entry (cost=0.00..28,055.06 rows=3,700 width=18) (actual rows=3,914 loops=3)

  • Output: ledger_entry.id, ledger_entry.posted_date, ledger_entry.journal_id, ledger_entry.account_id, ledger_entry.value, ledger_entry.is_credit, ledger_entry.description, ledger_entry.invoice_id, ledger_entry.currency, ledger_entry.product_id, ledger_entry.corrected, ledger_entry.correction_for_ledger_entry_id, ledger_entry.customer_rebate_id
  • Filter: ((ledger_entry.posted_date >= '2018-03-19'::date) AND (ledger_entry.posted_date <= '2018-04-01'::date))
  • Rows Removed by Filter: 397960
  • Buffers: shared hit=20433
  • Worker 0: actual rows=230 loops=1
  • Buffers: shared hit=4872
  • Worker 1: actual rows=2704 loops=1
  • Buffers: shared hit=4801
11. 0.000 0.000 ↓ 17.1 10,540 3 / 3

Hash (cost=548.21..548.21 rows=615 width=20) (actual rows=10,540 loops=3)

  • Output: billing_account.account_group, billing_account.id, billing_account.customer_id
  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 705kB
  • Buffers: shared hit=743
  • Worker 0: actual rows=10540 loops=1
  • Buffers: shared hit=248
  • Worker 1: actual rows=10540 loops=1
  • Buffers: shared hit=248
12. 0.000 0.000 ↓ 17.1 10,540 3 / 3

Bitmap Heap Scan on public.billing_account (cost=21.92..548.21 rows=615 width=20) (actual rows=10,540 loops=3)

  • Output: billing_account.account_group, billing_account.id, billing_account.customer_id
  • Recheck Cond: (billing_account.account_group = ANY ('{MONTHLY_FEE_INCOME,COMMISSION_INCOME,FEE_PER_E_CODE_INCOME,ADMIN_FEE_INCOME}'::accountgroupenum[]))
  • Heap Blocks: exact=204
  • Buffers: shared hit=743
  • Worker 0: actual rows=10540 loops=1
  • Buffers: shared hit=248
  • Worker 1: actual rows=10540 loops=1
  • Buffers: shared hit=248
13. 0.000 0.000 ↓ 17.1 10,540 3 / 3

Bitmap Index Scan on billing_account_account_group_idx (cost=0.00..21.77 rows=615 width=0) (actual rows=10,540 loops=3)

  • Index Cond: (billing_account.account_group = ANY ('{MONTHLY_FEE_INCOME,COMMISSION_INCOME,FEE_PER_E_CODE_INCOME,ADMIN_FEE_INCOME}'::accountgroupenum[]))
  • Buffers: shared hit=131
  • Worker 0: actual rows=10540 loops=1
  • Buffers: shared hit=44
  • Worker 1: actual rows=10540 loops=1
  • Buffers: shared hit=44
14. 0.000 0.000 ↑ 1.0 155 3 / 3

Hash (cost=4.55..4.55 rows=155 width=21) (actual rows=155 loops=3)

  • Output: customer.id, customer.name
  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
  • Buffers: shared hit=9
  • Worker 0: actual rows=155 loops=1
  • Buffers: shared hit=3
  • Worker 1: actual rows=155 loops=1
  • Buffers: shared hit=3
15. 0.000 0.000 ↑ 1.0 155 3 / 3

Seq Scan on public.customer (cost=0.00..4.55 rows=155 width=21) (actual rows=155 loops=3)

  • Output: customer.id, customer.name
  • Buffers: shared hit=9
  • Worker 0: actual rows=155 loops=1
  • Buffers: shared hit=3
  • Worker 1: actual rows=155 loops=1
  • Buffers: shared hit=3
16. 0.000 0.000 ↑ 3.4 13 1

Finalize GroupAggregate (cost=29,476.48..29,482.13 rows=44 width=61) (actual rows=13 loops=1)

  • Output: customer_1.id, customer_1.name, ledger_entry_1.currency, billing_account_1.account_group, sum(CASE WHEN ledger_entry_1.is_credit THEN ledger_entry_1.value ELSE ('-1'::numeric * ledger_entry_1.value) END)
  • Group Key: customer_1.id, ledger_entry_1.currency, billing_account_1.account_group
  • Buffers: shared hit=10336
17. 0.000 0.000 ↑ 1.8 20 1

Gather Merge (cost=29,476.48..29,481.13 rows=36 width=61) (actual rows=20 loops=1)

  • Output: customer_1.id, ledger_entry_1.currency, billing_account_1.account_group, customer_1.name, (PARTIAL sum(CASE WHEN ledger_entry_1.is_credit THEN ledger_entry_1.value ELSE ('-1'::numeric * ledger_entry_1.value) END))
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=22359
18. 0.000 0.000 ↑ 2.6 7 3 / 3

Partial GroupAggregate (cost=28,476.46..28,476.96 rows=18 width=61) (actual rows=7 loops=3)

  • Output: customer_1.id, ledger_entry_1.currency, billing_account_1.account_group, customer_1.name, PARTIAL sum(CASE WHEN ledger_entry_1.is_credit THEN ledger_entry_1.value ELSE ('-1'::numeric * ledger_entry_1.value) END)
  • Group Key: customer_1.id, ledger_entry_1.currency, billing_account_1.account_group
  • Buffers: shared hit=22359
  • Worker 0: actual rows=3 loops=1
  • Buffers: shared hit=6419
  • Worker 1: actual rows=9 loops=1
  • Buffers: shared hit=5604
19. 0.000 0.000 ↓ 3.2 58 3 / 3

Sort (cost=28,476.46..28,476.51 rows=18 width=35) (actual rows=58 loops=3)

  • Output: customer_1.id, ledger_entry_1.currency, billing_account_1.account_group, customer_1.name, ledger_entry_1.is_credit, ledger_entry_1.value
  • Sort Key: customer_1.id, ledger_entry_1.currency
  • Sort Method: quicksort Memory: 37kB
  • Buffers: shared hit=22359
  • Worker 0: actual rows=20 loops=1
  • Buffers: shared hit=6419
  • Worker 1: actual rows=48 loops=1
  • Buffers: shared hit=5604
20. 0.000 0.000 ↓ 3.2 58 3 / 3

Nested Loop (cost=336.31..28,476.08 rows=18 width=35) (actual rows=58 loops=3)

  • Output: customer_1.id, ledger_entry_1.currency, billing_account_1.account_group, customer_1.name, ledger_entry_1.is_credit, ledger_entry_1.value
  • Inner Unique: true
  • Buffers: shared hit=22329
  • Worker 0: actual rows=20 loops=1
  • Buffers: shared hit=6404
  • Worker 1: actual rows=48 loops=1
  • Buffers: shared hit=5589
21. 0.000 0.000 ↓ 3.2 58 3 / 3

Nested Loop (cost=336.16..28,473.16 rows=18 width=22) (actual rows=58 loops=3)

  • Output: ledger_entry_1.currency, ledger_entry_1.is_credit, ledger_entry_1.value, "order".customer_id, billing_account_1.account_group
  • Inner Unique: true
  • Buffers: shared hit=21977
  • Worker 0: actual rows=20 loops=1
  • Buffers: shared hit=6363
  • Worker 1: actual rows=48 loops=1
  • Buffers: shared hit=5492
22. 0.000 0.000 ↓ 3.2 58 3 / 3

Nested Loop (cost=335.74..28,464.77 rows=18 width=22) (actual rows=58 loops=3)

  • Output: ledger_entry_1.currency, ledger_entry_1.is_credit, ledger_entry_1.value, journal_entry.order_id, billing_account_1.account_group
  • Inner Unique: true
  • Buffers: shared hit=21273
  • Worker 0: actual rows=20 loops=1
  • Buffers: shared hit=6282
  • Worker 1: actual rows=48 loops=1
  • Buffers: shared hit=5299
23. 0.000 0.000 ↓ 3.2 58 3 / 3

Hash Join (cost=335.32..28,400.10 rows=18 width=22) (actual rows=58 loops=3)

  • Output: ledger_entry_1.currency, ledger_entry_1.is_credit, ledger_entry_1.value, ledger_entry_1.journal_id, billing_account_1.account_group
  • Inner Unique: true
  • Hash Cond: (ledger_entry_1.account_id = billing_account_1.id)
  • Buffers: shared hit=20571
  • Worker 0: actual rows=20 loops=1
  • Buffers: shared hit=6201
  • Worker 1: actual rows=48 loops=1
  • Buffers: shared hit=5106
24. 0.000 0.000 ↓ 1.1 3,914 3 / 3

Parallel Seq Scan on public.ledger_entry ledger_entry_1 (cost=0.00..28,055.06 rows=3,700 width=26) (actual rows=3,914 loops=3)

  • Output: ledger_entry_1.id, ledger_entry_1.posted_date, ledger_entry_1.journal_id, ledger_entry_1.account_id, ledger_entry_1.value, ledger_entry_1.is_credit, ledger_entry_1.description, ledger_entry_1.invoice_id, ledger_entry_1.currency, ledger_entry_1.product_id, ledger_entry_1.corrected, ledger_entry_1.correction_for_ledger_entry_id, ledger_entry_1.customer_rebate_id
  • Filter: ((ledger_entry_1.posted_date >= '2018-03-19'::date) AND (ledger_entry_1.posted_date <= '2018-04-01'::date))
  • Rows Removed by Filter: 397960
  • Buffers: shared hit=20433
  • Worker 0: actual rows=1361 loops=1
  • Buffers: shared hit=6152
  • Worker 1: actual rows=2073 loops=1
  • Buffers: shared hit=5057
25. 0.000 0.000 ↓ 4.9 757 3 / 3

Hash (cost=333.40..333.40 rows=154 width=12) (actual rows=757 loops=3)

  • Output: billing_account_1.account_group, billing_account_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
  • Buffers: shared hit=122
  • Worker 0: actual rows=757 loops=1
  • Buffers: shared hit=41
  • Worker 1: actual rows=757 loops=1
  • Buffers: shared hit=41
26. 0.000 0.000 ↓ 4.9 757 3 / 3

Bitmap Heap Scan on public.billing_account billing_account_1 (cost=5.48..333.40 rows=154 width=12) (actual rows=757 loops=3)

  • Output: billing_account_1.account_group, billing_account_1.id
  • Recheck Cond: (billing_account_1.account_group = 'PRODUCT_DISCOUNT_MARGIN'::accountgroupenum)
  • Heap Blocks: exact=35
  • Buffers: shared hit=122
  • Worker 0: actual rows=757 loops=1
  • Buffers: shared hit=41
  • Worker 1: actual rows=757 loops=1
  • Buffers: shared hit=41
27. 0.000 0.000 ↓ 4.9 757 3 / 3

Bitmap Index Scan on billing_account_account_group_idx (cost=0.00..5.44 rows=154 width=0) (actual rows=757 loops=3)

  • Index Cond: (billing_account_1.account_group = 'PRODUCT_DISCOUNT_MARGIN'::accountgroupenum)
  • Buffers: shared hit=17
  • Worker 0: actual rows=757 loops=1
  • Buffers: shared hit=6
  • Worker 1: actual rows=757 loops=1
  • Buffers: shared hit=6
28. 0.000 0.000 ↑ 1.0 1 175 / 3

Index Scan using journal_entry_pkey on public.journal_entry (cost=0.42..3.59 rows=1 width=16) (actual rows=1 loops=175)

  • Output: journal_entry.id, journal_entry.transaction_date, journal_entry.posted_date, journal_entry.order_id, journal_entry.statement_line_id, journal_entry.description, journal_entry.stocked_e_code_id, journal_entry.invoice_id, journal_entry.remittance_id, journal_entry.customer_rebate_id, journal_entry.order_item_id
  • Index Cond: (journal_entry.id = ledger_entry_1.journal_id)
  • Buffers: shared hit=702
  • Worker 0: actual rows=1 loops=20
  • Buffers: shared hit=81
  • Worker 1: actual rows=1 loops=48
  • Buffers: shared hit=193
29. 0.000 0.000 ↑ 1.0 1 175 / 3

Index Scan using order_pkey on public."order" (cost=0.42..0.47 rows=1 width=16) (actual rows=1 loops=175)

  • Output: "order".id, "order".customer_id
  • Index Cond: ("order".id = journal_entry.order_id)
  • Buffers: shared hit=704
  • Worker 0: actual rows=1 loops=20
  • Buffers: shared hit=81
  • Worker 1: actual rows=1 loops=48
  • Buffers: shared hit=193
30. 0.000 0.000 ↑ 1.0 1 175 / 3

Index Scan using customer_pkey on public.customer customer_1 (cost=0.14..0.16 rows=1 width=21) (actual rows=1 loops=175)

  • Output: customer_1.id, customer_1.name
  • Index Cond: (customer_1.id = "order".customer_id)
  • Buffers: shared hit=352
  • Worker 0: actual rows=1 loops=20
  • Buffers: shared hit=41
  • Worker 1: actual rows=1 loops=48
  • Buffers: shared hit=97