explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dJYg

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 4,667.899 ↑ 1.4 73 1

Limit (cost=369,149.60..369,152.08 rows=100 width=5) (actual time=4,667.735..4,667.899 rows=73 loops=1)

2. 0.106 4,667.894 ↑ 7.2 73 1

Unique (cost=369,149.60..369,162.70 rows=528 width=5) (actual time=4,667.734..4,667.894 rows=73 loops=1)

3. 0.746 4,667.788 ↑ 1.9 1,389 1

Sort (cost=369,149.60..369,156.15 rows=2,620 width=5) (actual time=4,667.732..4,667.788 rows=1,389 loops=1)

  • Sort Key: j8_entity_id.code
  • Sort Method: quicksort Memory: 114kB
4. 0.682 4,667.042 ↑ 1.9 1,389 1

Nested Loop (cost=263,679.75..369,000.85 rows=2,620 width=5) (actual time=3,717.255..4,667.042 rows=1,389 loops=1)

5. 0.683 4,663.582 ↑ 1.9 1,389 1

Nested Loop (cost=263,679.75..368,897.32 rows=2,620 width=21) (actual time=3,717.246..4,663.582 rows=1,389 loops=1)

6. 0.908 4,661.510 ↑ 1.9 1,389 1

Nested Loop (cost=263,679.75..368,817.81 rows=2,664 width=32) (actual time=3,717.238..4,661.510 rows=1,389 loops=1)

7. 447.436 4,656.658 ↑ 1.8 1,972 1

Hash Right Join (cost=263,679.75..368,625.45 rows=3,642 width=48) (actual time=3,717.227..4,656.658 rows=1,972 loops=1)

  • Hash Cond: (predicted_cla_compte_tva_pro.source_id = t.id)
  • Filter: (((CASE WHEN validated_cla_plan_compta_pro.is_training THEN 'VALIDATED'::text WHEN (predicted_cla_plan_compta_pro.ratio < '0.9'::double precision) THEN 'LOW'::text WHEN (predicted_cla_plan_compta_pro.ratio >= '0.9'::double precision) THEN 'HIGH'::text ELSE 'NONE'::text END = 'LOW'::text) AND (hashed SubPlan 1)) OR ((CASE WHEN validated_cla_compte_tva_pro.is_training THEN 'VALIDATED'::text WHEN (predicted_cla_compte_tva_pro.ratio < '0.9'::double precision) THEN 'LOW'::text WHEN (predicted_cla_compte_tva_pro.ratio >= '0.9'::double precision) THEN 'HIGH'::text ELSE 'NONE'::text END = 'LOW'::text) AND (hashed SubPlan 2)))
  • Rows Removed by Filter: 729664
8. 493.940 493.940 ↓ 1.0 731,186 1

Index Scan using classification_label_idx on classification predicted_cla_compte_tva_pro (cost=0.43..73,040.84 rows=729,264 width=24) (actual time=0.039..493.940 rows=731,186 loops=1)

  • Index Cond: (classification_label = 'compte_tva_pro'::text)
  • Filter: (NOT is_training)
  • Rows Removed by Filter: 576859
9. 227.062 3,715.273 ↓ 1.0 731,636 1

Hash (cost=254,636.76..254,636.76 rows=723,397 width=74) (actual time=3,715.273..3,715.273 rows=731,636 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 87261kB
10. 525.986 3,488.211 ↓ 1.0 731,636 1

Hash Right Join (cost=172,536.24..254,636.76 rows=723,397 width=74) (actual time=2,469.504..3,488.211 rows=731,636 loops=1)

  • Hash Cond: (predicted_cla_plan_compta_pro.source_id = t.id)
11. 494.494 494.494 ↓ 1.0 718,207 1

Index Scan using classification_label_idx on classification predicted_cla_plan_compta_pro (cost=0.43..72,288.01 rows=713,669 width=24) (actual time=0.027..494.494 rows=718,207 loops=1)

  • Index Cond: (classification_label = 'plan_compta_pro'::text)
  • Filter: (NOT is_training)
  • Rows Removed by Filter: 562555
12. 241.799 2,467.731 ↓ 1.0 731,636 1

Hash (cost=163,493.35..163,493.35 rows=723,397 width=66) (actual time=2,467.731..2,467.731 rows=731,636 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 77896kB
13. 276.553 2,225.932 ↓ 1.0 731,636 1

Merge Left Join (cost=1.54..163,493.35 rows=723,397 width=66) (actual time=0.072..2,225.932 rows=731,636 loops=1)

  • Merge Cond: (t.id = validated_cla_compte_tva_pro.source_id)
14. 238.206 1,658.366 ↓ 1.0 731,636 1

Merge Left Join (cost=0.98..98,493.57 rows=723,397 width=65) (actual time=0.058..1,658.366 rows=731,636 loops=1)

  • Merge Cond: (t.id = validated_cla_plan_compta_pro.source_id)
15. 1,134.792 1,134.792 ↓ 1.0 731,636 1

Index Scan using invoice_line_pkey on invoice_line t (cost=0.42..33,772.38 rows=723,397 width=64) (actual time=0.023..1,134.792 rows=731,636 loops=1)

16. 285.368 285.368 ↑ 1.0 562,555 1

Index Only Scan using classification_pkey on classification validated_cla_plan_compta_pro (cost=0.56..55,829.13 rows=566,685 width=17) (actual time=0.031..285.368 rows=562,555 loops=1)

  • Index Cond: ((classification_label = 'plan_compta_pro'::text) AND (is_training = true))
  • Filter: is_training
  • Heap Fetches: 12257
17. 291.013 291.013 ↑ 1.0 576,859 1

Index Only Scan using classification_pkey on classification validated_cla_compte_tva_pro (cost=0.56..55,952.95 rows=579,067 width=17) (actual time=0.012..291.013 rows=576,859 loops=1)

  • Index Cond: ((classification_label = 'compte_tva_pro'::text) AND (is_training = true))
  • Filter: is_training
  • Heap Fetches: 11436
18.          

SubPlan (forHash Right Join)

19. 0.004 0.004 ↑ 1.0 3 1

Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=32) (actual time=0.003..0.004 rows=3 loops=1)

20. 0.005 0.005 ↑ 1.0 3 1

Values Scan on "*VALUES*_1" (cost=0.00..0.04 rows=3 width=32) (actual time=0.004..0.005 rows=3 loops=1)

21. 3.944 3.944 ↑ 1.0 1 1,972

Index Scan using document_id_idx on document j3_document_id (cost=0.00..0.05 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,972)

  • Index Cond: (id = t.document_id)
  • Filter: ((NOT deleted) AND (error_status = 'VALIDATED'::text))
  • Rows Removed by Filter: 0
22. 1.389 1.389 ↑ 1.0 1 1,389

Index Scan using entity_id_idx on entity j8_entity_id (cost=0.00..0.03 rows=1 width=21) (actual time=0.001..0.001 rows=1 loops=1,389)

  • Index Cond: (id = t.entity_id)
  • Filter: ((code IS NOT NULL) AND (type_activite = 'PRO'::text))
23. 2.778 2.778 ↑ 1.0 1 1,389

Index Scan using invoice_id_idx on invoice j9_invoice_id (cost=0.00..0.04 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,389)

  • Index Cond: (id = t.invoice_id)
  • Filter: (source_file = 'pdf'::text)
Planning time : 7.103 ms
Execution time : 4,668.874 ms