explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FlV2

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.004 240.985 ↑ 1.0 1 1

Nested Loop (cost=14,765.33..14,765.38 rows=1 width=32) (actual time=240.984..240.985 rows=1 loops=1)

2.          

CTE debits

3. 0.012 124.137 ↑ 1.0 1 1

Aggregate (cost=7,382.65..7,382.66 rows=1 width=32) (actual time=124.137..124.137 rows=1 loops=1)

4. 0.000 124.125 ↓ 3.0 3 1

Nested Loop (cost=1,030.76..7,382.65 rows=1 width=10) (actual time=115.291..124.125 rows=3 loops=1)

5. 5.488 124.160 ↓ 3.0 3 1

Gather (cost=1,030.48..7,374.34 rows=1 width=14) (actual time=115.280..124.160 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 64.489 118.672 ↑ 1.0 1 3

Nested Loop (cost=30.48..6,374.24 rows=1 width=14) (actual time=108.143..118.672 rows=1 loops=3)

7. 13.130 54.181 ↓ 9,198.0 36,792 3

Hash Join (cost=30.19..6,372.87 rows=4 width=14) (actual time=0.303..54.181 rows=36,792 loops=3)

  • Hash Cond: (rc.currency_id = r.id)
8. 28.244 40.967 ↓ 180.6 147,169 3

Hash Join (cost=25.95..6,366.45 rows=815 width=18) (actual time=0.056..40.967 rows=147,169 loops=3)

  • Hash Cond: (rj.reporting_currency_id = rc.id)
9. 12.701 12.701 ↑ 1.2 147,169 3

Parallel Seq Scan on reporting_journals rj (cost=0.00..5,885.93 rows=172,593 width=18) (actual time=0.017..12.701 rows=147,169 loops=3)

10. 0.005 0.022 ↑ 1.5 4 3

Hash (cost=25.88..25.88 rows=6 width=12) (actual time=0.022..0.022 rows=4 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 0.017 0.017 ↑ 1.5 4 3

Seq Scan on reporting_currencies rc (cost=0.00..25.88 rows=6 width=12) (actual time=0.014..0.017 rows=4 loops=3)

  • Filter: (internal_organization_id = 1)
12. 0.006 0.084 ↑ 1.0 1 3

Hash (cost=4.22..4.22 rows=1 width=8) (actual time=0.083..0.084 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.078 0.078 ↑ 1.0 1 3

Seq Scan on currencies r (cost=0.00..4.22 rows=1 width=8) (actual time=0.037..0.078 rows=1 loops=3)

  • Filter: (alphabetic_code = 'MXN'::text)
  • Rows Removed by Filter: 177
14. 0.002 0.002 ↓ 0.0 0 110,377

Index Scan using accounting_transaction_details_pkey on accounting_transaction_details atd (cost=0.29..0.34 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=110,377)

  • Index Cond: (id = rj.accounting_transaction_detail_id)
  • Filter: ((NOT debit_credit_flag) AND (transaction_date <= '2018-12-31 00:00:00'::timestamp without time zone) AND (organization_gl_account_id = 34067))
  • Rows Removed by Filter: 1
15. 0.015 0.015 ↑ 1.0 1 3

Index Only Scan using ogla_id_parent_id on organization_gl_accounts ogla (cost=0.28..8.30 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=3)

  • Index Cond: (id = 34067)
  • Heap Fetches: 3
16.          

CTE credits

17. 0.004 116.838 ↑ 1.0 1 1

Aggregate (cost=7,382.65..7,382.66 rows=1 width=32) (actual time=116.838..116.838 rows=1 loops=1)

18. 0.000 116.834 ↓ 0.0 0 1

Nested Loop (cost=1,030.76..7,382.65 rows=1 width=10) (actual time=116.834..116.834 rows=0 loops=1)

19. 3.826 118.286 ↓ 0.0 0 1

Gather (cost=1,030.48..7,374.34 rows=1 width=14) (actual time=116.833..118.286 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
20. 62.418 114.460 ↓ 0.0 0 3

Nested Loop (cost=30.48..6,374.24 rows=1 width=14) (actual time=114.460..114.460 rows=0 loops=3)

21. 12.526 52.040 ↓ 9,198.0 36,792 3

Hash Join (cost=30.19..6,372.87 rows=4 width=14) (actual time=0.104..52.040 rows=36,792 loops=3)

  • Hash Cond: (rc_1.currency_id = r_1.id)
22. 27.657 39.486 ↓ 180.6 147,169 3

Hash Join (cost=25.95..6,366.45 rows=815 width=18) (actual time=0.021..39.486 rows=147,169 loops=3)

  • Hash Cond: (rj_1.reporting_currency_id = rc_1.id)
23. 11.821 11.821 ↑ 1.2 147,169 3

Parallel Seq Scan on reporting_journals rj_1 (cost=0.00..5,885.93 rows=172,593 width=18) (actual time=0.006..11.821 rows=147,169 loops=3)

24. 0.002 0.008 ↑ 1.5 4 3

Hash (cost=25.88..25.88 rows=6 width=12) (actual time=0.008..0.008 rows=4 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.006 0.006 ↑ 1.5 4 3

Seq Scan on reporting_currencies rc_1 (cost=0.00..25.88 rows=6 width=12) (actual time=0.006..0.006 rows=4 loops=3)

  • Filter: (internal_organization_id = 1)
26. 0.002 0.028 ↑ 1.0 1 3

Hash (cost=4.22..4.22 rows=1 width=8) (actual time=0.028..0.028 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.026 0.026 ↑ 1.0 1 3

Seq Scan on currencies r_1 (cost=0.00..4.22 rows=1 width=8) (actual time=0.013..0.026 rows=1 loops=3)

  • Filter: (alphabetic_code = 'MXN'::text)
  • Rows Removed by Filter: 177
28. 0.002 0.002 ↓ 0.0 0 110,377

Index Scan using accounting_transaction_details_pkey on accounting_transaction_details atd_1 (cost=0.29..0.34 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=110,377)

  • Index Cond: (id = rj_1.accounting_transaction_detail_id)
  • Filter: (debit_credit_flag AND (transaction_date <= '2018-12-31 00:00:00'::timestamp without time zone) AND (organization_gl_account_id = 34067))
  • Rows Removed by Filter: 1
29. 0.000 0.000 ↓ 0.0 0

Index Only Scan using ogla_id_parent_id on organization_gl_accounts ogla_1 (cost=0.28..8.30 rows=1 width=8) (never executed)

  • Index Cond: (id = 34067)
  • Heap Fetches: 0
30. 124.140 124.140 ↑ 1.0 1 1

CTE Scan on debits (cost=0.00..0.02 rows=1 width=32) (actual time=124.140..124.140 rows=1 loops=1)

31. 116.841 116.841 ↑ 1.0 1 1

CTE Scan on credits (cost=0.00..0.02 rows=1 width=32) (actual time=116.840..116.841 rows=1 loops=1)

Planning time : 3.297 ms
Execution time : 242.726 ms