explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DdNH : Optimization for: plan #FlV2

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.005 43.511 ↑ 1.0 1 1

Nested Loop (cost=14,819.62..14,819.68 rows=1 width=32) (actual time=43.510..43.511 rows=1 loops=1)

2.          

CTE debits

3. 0.009 40.343 ↑ 1.0 1 1

Aggregate (cost=7,409.80..7,409.81 rows=1 width=32) (actual time=40.343..40.343 rows=1 loops=1)

4. 0.000 40.334 ↓ 3.0 3 1

Nested Loop (cost=1,062.59..7,409.80 rows=1 width=10) (actual time=37.861..40.334 rows=3 loops=1)

5. 5.555 40.378 ↓ 3.0 3 1

Gather (cost=1,062.31..7,401.49 rows=1 width=14) (actual time=37.844..40.378 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 0.109 34.823 ↑ 1.0 1 3

Hash Join (cost=62.31..6,401.39 rows=1 width=14) (actual time=31.810..34.823 rows=1 loops=3)

  • Hash Cond: (rc.currency_id = r.id)
7. 0.016 34.650 ↓ 2.0 4 3

Hash Join (cost=58.07..6,397.14 rows=2 width=18) (actual time=31.638..34.650 rows=4 loops=3)

  • Hash Cond: (rj.reporting_currency_id = rc.id)
8. 16.304 34.618 ↑ 2.8 4 3

Hash Join (cost=57.01..6,396.02 rows=11 width=18) (actual time=31.608..34.618 rows=4 loops=3)

  • Hash Cond: (rj.accounting_transaction_detail_id = atd.id)
9. 18.260 18.260 ↑ 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.012..18.260 rows=147,169 loops=3)

10. 0.003 0.054 ↑ 2.3 3 3

Hash (cost=56.92..56.92 rows=7 width=12) (actual time=0.054..0.054 rows=3 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 0.025 0.051 ↑ 2.3 3 3

Bitmap Heap Scan on accounting_transaction_details atd (cost=4.40..56.92 rows=7 width=12) (actual time=0.039..0.051 rows=3 loops=3)

  • Recheck Cond: (organization_gl_account_id = 34067)
  • Filter: ((NOT debit_credit_flag) AND (transaction_date <= '2018-12-31 00:00:00'::timestamp without time zone))
  • Heap Blocks: exact=3
12. 0.026 0.026 ↑ 4.7 3 3

Bitmap Index Scan on atd_ogla_idx (cost=0.00..4.40 rows=14 width=0) (actual time=0.026..0.026 rows=3 loops=3)

  • Index Cond: (organization_gl_account_id = 34067)
13. 0.004 0.016 ↓ 4.0 4 3

Hash (cost=1.05..1.05 rows=1 width=12) (actual time=0.016..0.016 rows=4 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.012 0.012 ↓ 4.0 4 3

Seq Scan on reporting_currencies rc (cost=0.00..1.05 rows=1 width=12) (actual time=0.010..0.012 rows=4 loops=3)

  • Filter: (internal_organization_id = 1)
15. 0.004 0.064 ↑ 1.0 1 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.060 0.060 ↑ 1.0 1 3

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

  • Filter: (alphabetic_code = 'MXN'::text)
  • Rows Removed by Filter: 177
17. 0.021 0.021 ↑ 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.007..0.007 rows=1 loops=3)

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

CTE credits

19. 0.014 3.156 ↑ 1.0 1 1

Aggregate (cost=7,409.80..7,409.81 rows=1 width=32) (actual time=3.156..3.156 rows=1 loops=1)

20. 0.000 3.142 ↓ 0.0 0 1

Nested Loop (cost=1,062.59..7,409.80 rows=1 width=10) (actual time=3.142..3.142 rows=0 loops=1)

21. 3.839 3.973 ↓ 0.0 0 1

Gather (cost=1,062.31..7,401.49 rows=1 width=14) (actual time=3.141..3.973 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
22. 0.056 0.134 ↓ 0.0 0 3

Hash Join (cost=62.31..6,401.39 rows=1 width=14) (actual time=0.134..0.134 rows=0 loops=3)

  • Hash Cond: (rc_1.currency_id = r_1.id)
23. 0.006 0.051 ↓ 0.0 0 3

Hash Join (cost=58.07..6,397.14 rows=2 width=18) (actual time=0.051..0.051 rows=0 loops=3)

  • Hash Cond: (rj_1.reporting_currency_id = rc_1.id)
24. 0.005 0.037 ↓ 0.0 0 3

Hash Join (cost=57.01..6,396.02 rows=11 width=18) (actual time=0.037..0.037 rows=0 loops=3)

  • Hash Cond: (rj_1.accounting_transaction_detail_id = atd_1.id)
25. 0.006 0.006 ↑ 172,593.0 1 3

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

26. 0.000 0.026 ↓ 0.0 0 3

Hash (cost=56.92..56.92 rows=7 width=12) (actual time=0.026..0.026 rows=0 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
27. 0.012 0.026 ↓ 0.0 0 3

Bitmap Heap Scan on accounting_transaction_details atd_1 (cost=4.40..56.92 rows=7 width=12) (actual time=0.026..0.026 rows=0 loops=3)

  • Recheck Cond: (organization_gl_account_id = 34067)
  • Filter: (debit_credit_flag AND (transaction_date <= '2018-12-31 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=3
28. 0.014 0.014 ↑ 4.7 3 3

Bitmap Index Scan on atd_ogla_idx (cost=0.00..4.40 rows=14 width=0) (actual time=0.014..0.014 rows=3 loops=3)

  • Index Cond: (organization_gl_account_id = 34067)
29. 0.002 0.008 ↓ 4.0 4 3

Hash (cost=1.05..1.05 rows=1 width=12) (actual time=0.008..0.008 rows=4 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.006 0.006 ↓ 4.0 4 3

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

  • Filter: (internal_organization_id = 1)
31. 0.002 0.027 ↑ 1.0 1 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.025 0.025 ↑ 1.0 1 3

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

  • Filter: (alphabetic_code = 'MXN'::text)
  • Rows Removed by Filter: 177
33. 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
34. 40.347 40.347 ↑ 1.0 1 1

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

35. 3.159 3.159 ↑ 1.0 1 1

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

Planning time : 3.258 ms
Execution time : 44.664 ms