explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y4uh

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 0.184 ↑ 1.0 1 1

Nested Loop (cost=211.51..211.56 rows=1 width=32) (actual time=0.184..0.184 rows=1 loops=1)

2.          

CTE debits

3. 0.007 0.122 ↑ 1.0 1 1

Aggregate (cost=105.74..105.75 rows=1 width=32) (actual time=0.122..0.122 rows=1 loops=1)

4. 0.002 0.115 ↓ 3.0 3 1

Nested Loop (cost=5.30..105.74 rows=1 width=10) (actual time=0.093..0.115 rows=3 loops=1)

5. 0.003 0.104 ↓ 3.0 3 1

Nested Loop (cost=5.02..97.43 rows=1 width=14) (actual time=0.087..0.104 rows=3 loops=1)

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

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

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

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

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

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

  • Filter: (alphabetic_code = 'GBP'::text)
  • Rows Removed by Filter: 134
9. 0.004 0.039 ↑ 2.3 12 1

Nested Loop (cost=5.02..91.79 rows=28 width=18) (actual time=0.022..0.039 rows=12 loops=1)

10. 0.007 0.017 ↑ 2.3 3 1

Bitmap Heap Scan on accounting_transaction_details atd (cost=4.59..31.39 rows=7 width=12) (actual time=0.013..0.017 rows=3 loops=1)

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

Bitmap Index Scan on accounting_transaction_details_oglaid_date_drcr_idx (cost=0.00..4.59 rows=7 width=0) (actual time=0.010..0.010 rows=3 loops=1)

  • Index Cond: ((organization_gl_account_id = 34067) AND (transaction_date <= '2018-12-31 00:00:00'::timestamp without time zone) AND (debit_credit_flag = false))
12. 0.018 0.018 ↑ 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.005..0.006 rows=4 loops=3)

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

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

CTE credits

15. 0.001 0.059 ↑ 1.0 1 1

Aggregate (cost=105.74..105.75 rows=1 width=32) (actual time=0.059..0.059 rows=1 loops=1)

16. 0.000 0.058 ↓ 0.0 0 1

Nested Loop (cost=5.30..105.74 rows=1 width=10) (actual time=0.058..0.058 rows=0 loops=1)

17. 0.001 0.058 ↓ 0.0 0 1

Nested Loop (cost=5.02..97.43 rows=1 width=14) (actual time=0.058..0.058 rows=0 loops=1)

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

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

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

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

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

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

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

Nested Loop (cost=5.02..91.79 rows=28 width=18) (actual time=0.003..0.003 rows=0 loops=1)

22. 0.000 0.003 ↓ 0.0 0 1

Bitmap Heap Scan on accounting_transaction_details atd_1 (cost=4.59..31.39 rows=7 width=12) (actual time=0.003..0.003 rows=0 loops=1)

  • Recheck Cond: ((organization_gl_account_id = 34067) AND (transaction_date <= '2018-12-31 00:00:00'::timestamp without time zone))
  • Filter: debit_credit_flag
23. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on accounting_transaction_details_oglaid_date_drcr_idx (cost=0.00..4.59 rows=7 width=0) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((organization_gl_account_id = 34067) AND (transaction_date <= '2018-12-31 00:00:00'::timestamp without time zone) AND (debit_credit_flag = true))
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.123 0.123 ↑ 1.0 1 1

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

27. 0.059 0.059 ↑ 1.0 1 1

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

Planning time : 1.213 ms
Execution time : 0.263 ms