explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FRxf

Settings
# exclusive inclusive rows x rows loops node
1. 497.323 8,332.714 ↓ 2.7 24 1

Limit (cost=3,652.64..666,167.50 rows=9 width=68) (actual time=821.601..8,332.714 rows=24 loops=1)

  • Functions: 42
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 4.369 ms, Inlining 9.573 ms, Optimization 300.621 ms, Emission 186.460 ms, Total 501.023 ms
2. 0.246 7,835.391 ↓ 2.7 24 1

Nested Loop (cost=3,652.64..666,167.50 rows=9 width=68) (actual time=324.287..7,835.391 rows=24 loops=1)

3. 78.072 90.033 ↓ 2.7 24 1

Bitmap Heap Scan on ecritures_compta_maj ecr (cost=3,652.22..41,561.90 rows=9 width=36) (actual time=28.919..90.033 rows=24 loops=1)

  • Recheck Cond: ((journal)::text = ANY ('{VEN,VEF,VEX}'::text[]))
  • Filter: ((code_affaire)::text ~~ 'M200000%'::text)
  • Rows Removed by Filter: 144,554
  • Heap Blocks: exact=22,174
4. 11.961 11.961 ↓ 1.0 144,578 1

Bitmap Index Scan on ecritures_compta_maj_journal_idx (cost=0.00..3,652.22 rows=140,657 width=0) (actual time=11.961..11.961 rows=144,578 loops=1)

  • Index Cond: ((journal)::text = ANY ('{VEN,VEF,VEX}'::text[]))
5. 0.432 0.432 ↑ 1.0 1 24

Index Only Scan using account_analytic_account_code_idx on account_analytic_account aff (cost=0.42..5.77 rows=1 width=9) (actual time=0.016..0.018 rows=1 loops=24)

  • Index Cond: (code = (ecr.code_affaire)::text)
  • Heap Fetches: 24
6.          

SubPlan (for Nested Loop)

7. 0.048 7,744.680 ↑ 1.0 1 24

Limit (cost=9.31..69,394.79 rows=1 width=4) (actual time=322.694..322.695 rows=1 loops=24)

8. 0.192 7,744.632 ↑ 1.0 1 24

Nested Loop (cost=9.31..69,394.79 rows=1 width=4) (actual time=322.693..322.693 rows=1 loops=24)

  • Join Filter: (cbld.budget_detail_id = abd.id)
9. 0.096 7,744.392 ↑ 1.0 1 24

Nested Loop (cost=9.31..69,393.66 rows=1 width=4) (actual time=322.683..322.683 rows=1 loops=24)

10. 1.746 7,744.032 ↑ 1.0 1 24

Nested Loop (cost=8.88..69,393.09 rows=1 width=4) (actual time=322.668..322.668 rows=1 loops=24)

11. 3,287.256 7,733.712 ↓ 19.8 119 24

Hash Join (cost=8.45..69,389.79 rows=6 width=8) (actual time=292.859..322.238 rows=119 loops=24)

  • Hash Cond: (ail.account_analytic_id = aa.id)
12. 4,446.240 4,446.240 ↑ 1.1 1,697,184 24

Seq Scan on account_invoice_line ail (cost=0.00..64,348.49 rows=1,917,249 width=12) (actual time=0.005..185.260 rows=1,697,184 loops=24)

13. 0.096 0.216 ↑ 1.0 1 24

Hash (cost=8.44..8.44 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=24)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 0.120 0.120 ↑ 1.0 1 24

Index Scan using account_analytic_account_code_idx on account_analytic_account aa (cost=0.42..8.44 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=24)

  • Index Cond: ((code)::text = (ecr.code_affaire)::text)
15. 8.574 8.574 ↓ 0.0 0 2,858

Index Scan using account_invoice_pkey on account_invoice iv (cost=0.42..0.55 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=2,858)

  • Index Cond: (id = ail.invoice_id)
  • Filter: (ltrim(rtrim((number)::text)) = CASE WHEN (("substring"(ltrim(rtrim((ecr.origine)::text)), 1, 2) = 'FA'::text) OR ("substring"(ltrim(rtrim((ecr.origine)::text)), 1, 4) = '0000'::text)) THEN "substring"(ltrim(rtrim((ecr.origine)::text)), 1, 10) ELSE ltrim(rtrim((ecr.origine)::text)) END)
  • Rows Removed by Filter: 1
16. 0.264 0.264 ↑ 1.0 1 24

Index Scan using crossovered_budget_lines_detail_pkey on crossovered_budget_lines_detail cbld (cost=0.43..0.57 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=24)

  • Index Cond: (id = ail.budget_detail_id)
17. 0.048 0.048 ↑ 6.0 1 24

Seq Scan on affair_budget_detail abd (cost=0.00..1.06 rows=6 width=8) (actual time=0.002..0.002 rows=1 loops=24)

Execution time : 8,337.260 ms