explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PCdx

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

Nested Loop (cost=15,101.94..15,102.00 rows=1 width=32) (actual time=30.744..30.746 rows=1 loops=1)

2.          

CTE debits

3. 0.000 15.770 ↑ 1.0 1 1

Finalize Aggregate (cost=7,558.79..7,558.80 rows=1 width=32) (actual time=15.770..15.770 rows=1 loops=1)

4. 2.288 15.817 ↓ 2.0 2 1

Gather (cost=7,558.67..7,558.78 rows=1 width=32) (actual time=15.609..15.817 rows=2 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
5. 0.015 13.529 ↑ 1.0 1 2

Partial Aggregate (cost=6,558.67..6,558.68 rows=1 width=32) (actual time=13.529..13.529 rows=1 loops=2)

6. 0.040 13.514 ↑ 22.0 1 2

Hash Join (cost=95.23..6,558.61 rows=22 width=10) (actual time=6.191..13.514 rows=1 loops=2)

  • Hash Cond: (rc.currency_id = r.id)
7. 0.007 13.448 ↑ 990.5 4 2

Hash Join (cost=90.99..6,543.73 rows=3,962 width=14) (actual time=6.125..13.448 rows=4 loops=2)

  • Hash Cond: (rj.reporting_currency_id = rc.id)
8. 0.004 13.434 ↑ 3,961.8 4 2

Nested Loop (cost=89.93..6,456.99 rows=15,847 width=14) (actual time=6.112..13.434 rows=4 loops=2)

9. 2.280 13.400 ↑ 3,963.0 1 2

Hash Join (cost=89.51..2,800.51 rows=3,963 width=8) (actual time=6.081..13.400 rows=1 loops=2)

  • Hash Cond: (atd.organization_gl_account_id = ogla.id)
10. 10.930 10.930 ↑ 1.2 26,328 2

Parallel Seq Scan on accounting_transaction_details atd (cost=0.00..2,625.85 rows=32,409 width=12) (actual time=0.005..10.930 rows=26,328 loops=2)

  • Filter: ((NOT debit_credit_flag) AND (transaction_date <= '2018-12-31 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 28860
11. 0.057 0.190 ↑ 1.0 590 2

Hash (cost=82.13..82.13 rows=590 width=8) (actual time=0.190..0.190 rows=590 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
12. 0.087 0.133 ↑ 1.0 590 2

Bitmap Heap Scan on organization_gl_accounts ogla (cost=17.29..82.13 rows=590 width=8) (actual time=0.050..0.133 rows=590 loops=2)

  • Recheck Cond: ((id = 34055) OR (parent_id = 34055))
  • Heap Blocks: exact=10
13. 0.002 0.046 ↓ 0.0 0 2

BitmapOr (cost=17.29..17.29 rows=590 width=0) (actual time=0.046..0.046 rows=0 loops=2)

14. 0.014 0.014 ↑ 1.0 1 2

Bitmap Index Scan on ogla_id_parent_id (cost=0.00..4.29 rows=1 width=0) (actual time=0.014..0.014 rows=1 loops=2)

  • Index Cond: (id = 34055)
15. 0.030 0.030 ↑ 1.0 589 2

Bitmap Index Scan on organization_gl_accounts_parent_idx (cost=0.00..12.70 rows=589 width=0) (actual time=0.030..0.030 rows=589 loops=2)

  • Index Cond: (parent_id = 34055)
16. 0.030 0.030 ↑ 1.0 4 2

Index Scan using reporting_journal_atdid_idx on reporting_journals rj (cost=0.42..0.88 rows=4 width=18) (actual time=0.029..0.030 rows=4 loops=2)

  • Index Cond: (accounting_transaction_detail_id = atd.id)
17. 0.002 0.007 ↓ 4.0 4 2

Hash (cost=1.05..1.05 rows=1 width=12) (actual time=0.007..0.007 rows=4 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.005 0.005 ↓ 4.0 4 2

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

  • Filter: (internal_organization_id = 1)
19. 0.002 0.026 ↑ 1.0 1 2

Hash (cost=4.22..4.22 rows=1 width=8) (actual time=0.026..0.026 rows=1 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.024 0.024 ↑ 1.0 1 2

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

  • Filter: (alphabetic_code = 'MXN'::text)
  • Rows Removed by Filter: 177
21.          

CTE credits

22. 0.000 14.967 ↑ 1.0 1 1

Finalize Aggregate (cost=7,543.14..7,543.15 rows=1 width=32) (actual time=14.967..14.967 rows=1 loops=1)

23. 2.739 15.932 ↓ 2.0 2 1

Gather (cost=7,543.02..7,543.13 rows=1 width=32) (actual time=14.829..15.932 rows=2 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
24. 0.026 13.193 ↑ 1.0 1 2

Partial Aggregate (cost=6,543.02..6,543.03 rows=1 width=32) (actual time=13.192..13.193 rows=1 loops=2)

25. 0.050 13.167 ↑ 22.0 1 2

Hash Join (cost=95.23..6,542.96 rows=22 width=10) (actual time=7.969..13.167 rows=1 loops=2)

  • Hash Cond: (rc_1.currency_id = r_1.id)
26. 0.009 13.091 ↑ 983.8 4 2

Hash Join (cost=90.99..6,528.15 rows=3,935 width=14) (actual time=7.893..13.091 rows=4 loops=2)

  • Hash Cond: (rj_1.reporting_currency_id = rc_1.id)
27. 0.008 13.074 ↑ 3,934.8 4 2

Nested Loop (cost=89.93..6,442.00 rows=15,739 width=14) (actual time=7.877..13.074 rows=4 loops=2)

28. 1.852 12.967 ↑ 3,936.0 1 2

Hash Join (cost=89.51..2,799.93 rows=3,936 width=8) (actual time=7.773..12.967 rows=1 loops=2)

  • Hash Cond: (atd_1.organization_gl_account_id = ogla_1.id)
29. 10.912 10.912 ↑ 1.2 26,396 2

Parallel Seq Scan on accounting_transaction_details atd_1 (cost=0.00..2,625.85 rows=32,189 width=12) (actual time=0.006..10.912 rows=26,396 loops=2)

  • Filter: (debit_credit_flag AND (transaction_date <= '2018-12-31 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 28792
30. 0.060 0.203 ↑ 1.0 590 2

Hash (cost=82.13..82.13 rows=590 width=8) (actual time=0.203..0.203 rows=590 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
31. 0.093 0.143 ↑ 1.0 590 2

Bitmap Heap Scan on organization_gl_accounts ogla_1 (cost=17.29..82.13 rows=590 width=8) (actual time=0.054..0.143 rows=590 loops=2)

  • Recheck Cond: ((id = 34055) OR (parent_id = 34055))
  • Heap Blocks: exact=10
32. 0.001 0.050 ↓ 0.0 0 2

BitmapOr (cost=17.29..17.29 rows=590 width=0) (actual time=0.050..0.050 rows=0 loops=2)

33. 0.016 0.016 ↑ 1.0 1 2

Bitmap Index Scan on ogla_id_parent_id (cost=0.00..4.29 rows=1 width=0) (actual time=0.016..0.016 rows=1 loops=2)

  • Index Cond: (id = 34055)
34. 0.033 0.033 ↑ 1.0 589 2

Bitmap Index Scan on organization_gl_accounts_parent_idx (cost=0.00..12.70 rows=589 width=0) (actual time=0.033..0.033 rows=589 loops=2)

  • Index Cond: (parent_id = 34055)
35. 0.099 0.099 ↑ 1.0 4 2

Index Scan using reporting_journal_atdid_idx on reporting_journals rj_1 (cost=0.42..0.89 rows=4 width=18) (actual time=0.097..0.099 rows=4 loops=2)

  • Index Cond: (accounting_transaction_detail_id = atd_1.id)
36. 0.002 0.008 ↓ 4.0 4 2

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.006 0.006 ↓ 4.0 4 2

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

  • Filter: (internal_organization_id = 1)
38. 0.001 0.026 ↑ 1.0 1 2

Hash (cost=4.22..4.22 rows=1 width=8) (actual time=0.026..0.026 rows=1 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
39. 0.025 0.025 ↑ 1.0 1 2

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

  • Filter: (alphabetic_code = 'MXN'::text)
  • Rows Removed by Filter: 177
40. 15.772 15.772 ↑ 1.0 1 1

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

41. 14.969 14.969 ↑ 1.0 1 1

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

Planning time : 1.248 ms
Execution time : 31.883 ms