explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m6pZ : Optimization for: Optimization for: plan #FlV2; plan #DdNH

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.006 0.676 ↑ 1.0 1 1

Nested Loop (cost=262.56..262.61 rows=1 width=32) (actual time=0.674..0.676 rows=1 loops=1)

2.          

CTE debits

3. 0.019 0.458 ↑ 1.0 1 1

Aggregate (cost=131.27..131.28 rows=1 width=32) (actual time=0.457..0.458 rows=1 loops=1)

4. 0.004 0.439 ↓ 3.0 3 1

Nested Loop (cost=5.10..131.26 rows=1 width=10) (actual time=0.239..0.439 rows=3 loops=1)

5. 0.009 0.405 ↓ 3.0 3 1

Nested Loop (cost=4.82..122.95 rows=1 width=14) (actual time=0.221..0.405 rows=3 loops=1)

  • Join Filter: (rj.reporting_currency_id = rc.id)
  • Rows Removed by Join Filter: 9
6. 0.011 0.204 ↑ 1.0 1 1

Nested Loop (cost=0.00..5.29 rows=1 width=8) (actual time=0.090..0.204 rows=1 loops=1)

  • Join Filter: (rc.currency_id = r.id)
  • Rows Removed by Join Filter: 3
7. 0.017 0.017 ↓ 4.0 4 1

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

  • Filter: (internal_organization_id = 1)
8. 0.176 0.176 ↑ 1.0 1 4

Seq Scan on currencies r (cost=0.00..4.22 rows=1 width=8) (actual time=0.009..0.044 rows=1 loops=4)

  • Filter: (alphabetic_code = 'MXN'::text)
  • Rows Removed by Filter: 134
9. 0.013 0.192 ↑ 2.3 12 1

Nested Loop (cost=4.82..117.32 rows=28 width=18) (actual time=0.123..0.192 rows=12 loops=1)

10. 0.022 0.041 ↑ 2.3 3 1

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

  • 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
11. 0.019 0.019 ↑ 4.7 3 1

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

  • Index Cond: (organization_gl_account_id = 34067)
12. 0.138 0.138 ↑ 1.0 4 3

Index Scan using reporting_journal_atdid_idx on reporting_journals rj (cost=0.42..8.59 rows=4 width=18) (actual time=0.043..0.046 rows=4 loops=3)

  • Index Cond: (accounting_transaction_detail_id = atd.id)
13. 0.030 0.030 ↑ 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.009..0.010 rows=1 loops=3)

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

CTE credits

15. 0.003 0.206 ↑ 1.0 1 1

Aggregate (cost=131.27..131.28 rows=1 width=32) (actual time=0.205..0.206 rows=1 loops=1)

16. 0.000 0.203 ↓ 0.0 0 1

Nested Loop (cost=5.10..131.26 rows=1 width=10) (actual time=0.203..0.203 rows=0 loops=1)

17. 0.004 0.203 ↓ 0.0 0 1

Nested Loop (cost=4.82..122.95 rows=1 width=14) (actual time=0.203..0.203 rows=0 loops=1)

  • Join Filter: (rj_1.reporting_currency_id = rc_1.id)
18. 0.007 0.178 ↑ 1.0 1 1

Nested Loop (cost=0.00..5.29 rows=1 width=8) (actual time=0.069..0.178 rows=1 loops=1)

  • Join Filter: (rc_1.currency_id = r_1.id)
  • Rows Removed by Join Filter: 3
19. 0.007 0.007 ↓ 4.0 4 1

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

  • Filter: (internal_organization_id = 1)
20. 0.164 0.164 ↑ 1.0 1 4

Seq Scan on currencies r_1 (cost=0.00..4.22 rows=1 width=8) (actual time=0.007..0.041 rows=1 loops=4)

  • Filter: (alphabetic_code = 'MXN'::text)
  • Rows Removed by Filter: 134
21. 0.000 0.021 ↓ 0.0 0 1

Nested Loop (cost=4.82..117.32 rows=28 width=18) (actual time=0.021..0.021 rows=0 loops=1)

22. 0.011 0.021 ↓ 0.0 0 1

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

  • 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
23. 0.010 0.010 ↑ 4.7 3 1

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

  • Index Cond: (organization_gl_account_id = 34067)
24. 0.000 0.000 ↓ 0.0 0

Index Scan using reporting_journal_atdid_idx on reporting_journals rj_1 (cost=0.42..8.59 rows=4 width=18) (never executed)

  • Index Cond: (accounting_transaction_detail_id = atd_1.id)
25. 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
26. 0.462 0.462 ↑ 1.0 1 1

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

27. 0.208 0.208 ↑ 1.0 1 1

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

Planning time : 3.731 ms
Execution time : 0.910 ms