explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7Hif

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 4,358.920 ↑ 1.4 74 1

Limit (cost=348,206.47..348,208.95 rows=100 width=5) (actual time=4,358.768..4,358.920 rows=74 loops=1)

2. 0.100 4,358.915 ↑ 7.1 74 1

Unique (cost=348,206.47..348,219.57 rows=528 width=5) (actual time=4,358.767..4,358.915 rows=74 loops=1)

3. 0.608 4,358.815 ↑ 1.9 1,403 1

Sort (cost=348,206.47..348,213.02 rows=2,620 width=5) (actual time=4,358.764..4,358.815 rows=1,403 loops=1)

  • Sort Key: j8_entity_id.code
  • Sort Method: quicksort Memory: 114kB
4. 0.456 4,358.207 ↑ 1.9 1,403 1

Nested Loop (cost=253,529.78..348,057.71 rows=2,620 width=5) (actual time=3,752.866..4,358.207 rows=1,403 loops=1)

5. 0.672 4,354.945 ↑ 1.9 1,403 1

Nested Loop (cost=253,529.78..347,954.18 rows=2,620 width=21) (actual time=3,752.858..4,354.945 rows=1,403 loops=1)

6. 0.629 4,352.870 ↑ 1.9 1,403 1

Nested Loop (cost=253,529.78..347,874.67 rows=2,664 width=32) (actual time=3,752.849..4,352.870 rows=1,403 loops=1)

7. 353.574 4,348.269 ↑ 1.8 1,986 1

Hash Right Join (cost=253,529.78..347,682.32 rows=3,642 width=48) (actual time=3,709.431..4,348.269 rows=1,986 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: 729739
8. 288.661 288.661 ↓ 1.0 731,275 1

Index Scan using classification_label_is_training_idx on classification predicted_cla_compte_tva_pro (cost=0.43..62,246.71 rows=729,286 width=24) (actual time=0.035..288.661 rows=731,275 loops=1)

  • Index Cond: ((classification_label = 'compte_tva_pro'::text) AND (is_training = false))
  • Filter: (NOT is_training)
9. 242.599 3,706.026 ↓ 1.0 731,725 1

Hash (cost=244,486.80..244,486.80 rows=723,397 width=74) (actual time=3,706.026..3,706.026 rows=731,725 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 87270kB
10. 589.332 3,463.427 ↓ 1.0 731,725 1

Hash Right Join (cost=172,845.58..244,486.80 rows=723,397 width=74) (actual time=2,511.769..3,463.427 rows=731,725 loops=1)

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

Index Scan using classification_label_is_training_idx on classification predicted_cla_plan_compta_pro (cost=0.43..61,828.39 rows=713,691 width=24) (actual time=0.031..364.011 rows=718,296 loops=1)

  • Index Cond: ((classification_label = 'plan_compta_pro'::text) AND (is_training = false))
  • Filter: (NOT is_training)
12. 241.792 2,510.084 ↓ 1.0 731,725 1

Hash (cost=163,802.69..163,802.69 rows=723,397 width=66) (actual time=2,510.084..2,510.084 rows=731,725 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 77904kB
13. 284.502 2,268.292 ↓ 1.0 731,725 1

Merge Left Join (cost=1.54..163,802.69 rows=723,397 width=66) (actual time=0.072..2,268.292 rows=731,725 loops=1)

  • Merge Cond: (t.id = validated_cla_compte_tva_pro.source_id)
14. 244.844 1,683.516 ↓ 1.0 731,725 1

Merge Left Join (cost=0.98..98,648.73 rows=723,397 width=65) (actual time=0.057..1,683.516 rows=731,725 loops=1)

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

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

16. 289.520 289.520 ↑ 1.0 562,555 1

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

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

Index Only Scan using classification_pkey on classification validated_cla_compte_tva_pro (cost=0.56..56,106.91 rows=579,085 width=17) (actual time=0.012..300.274 rows=576,859 loops=1)

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

SubPlan (forHash Right Join)

19. 0.006 0.006 ↑ 1.0 3 1

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

20. 0.002 0.002 ↑ 1.0 3 1

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

21. 3.972 3.972 ↑ 1.0 1 1,986

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,986)

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

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,403)

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

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,403)

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