explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TYg1

Settings
# exclusive inclusive rows x rows loops node
1. 0.044 96,124.832 ↓ 3.2 603 1

Limit (cost=6,363,466.00..6,363,473.12 rows=190 width=264) (actual time=96,124.424..96,124.832 rows=603 loops=1)

2. 0.320 96,124.788 ↓ 3.2 603 1

Unique (cost=6,363,466.00..6,363,473.12 rows=190 width=264) (actual time=96,124.423..96,124.788 rows=603 loops=1)

3. 3.321 96,124.468 ↓ 3.2 604 1

Sort (cost=6,363,466.00..6,363,466.47 rows=190 width=264) (actual time=96,124.422..96,124.468 rows=604 loops=1)

  • Sort Key: ((SubPlan 3)), ((SubPlan 4)), ((SubPlan 2)), commandeve3_.numerocommandevente, cliententi5_1_.abrege, commandeve3_.datecommandevente, articleent2_.code, lignecomma0_.referencechantier, lignecomma1_1_.quantitevente_valeur, uniteentit6_.libelle, lignecomma1_2_.commentaire, lignecomma1_.datelivraisonacceptee, lignecomma0_.heureenlevement, commandeve3_.etatlivraison
  • Sort Method: quicksort Memory: 168kB
4. 8.787 96,121.147 ↓ 3.2 604 1

Nested Loop Left Join (cost=1.43..6,363,458.81 rows=190 width=264) (actual time=185.693..96,121.147 rows=604 loops=1)

  • Join Filter: ((lignecomma1_1_.id_unitevente)::text = (uniteentit6_.id)::text)
  • Rows Removed by Join Filter: 8456
  • Filter: (uniteentit6_.datefinvalidite IS NULL)
5. 1.844 110.184 ↓ 2.5 604 1

Nested Loop Left Join (cost=1.43..7,476.09 rows=238 width=285) (actual time=12.528..110.184 rows=604 loops=1)

  • Filter: (articleent2_.datefinvalidite IS NULL)
6. 1.830 104.112 ↓ 2.5 604 1

Nested Loop Left Join (cost=1.43..7,409.97 rows=238 width=311) (actual time=12.516..104.112 rows=604 loops=1)

7. 2.247 96.846 ↓ 2.5 604 1

Nested Loop Left Join (cost=1.43..7,201.22 rows=238 width=273) (actual time=12.494..96.846 rows=604 loops=1)

  • Filter: (lignecomma1_2_.datefinvalidite IS NULL)
  • Rows Removed by Filter: 22
8. 1.779 88.339 ↓ 2.6 626 1

Nested Loop (cost=1.43..6,801.09 rows=242 width=119) (actual time=5.833..88.339 rows=626 loops=1)

9. 1.588 75.668 ↓ 3.1 778 1

Nested Loop (cost=1.43..6,666.38 rows=248 width=69) (actual time=4.673..75.668 rows=778 loops=1)

10. 3.863 66.928 ↓ 3.3 596 1

Nested Loop Left Join (cost=1.43..5,976.84 rows=180 width=64) (actual time=1.011..66.928 rows=596 loops=1)

  • Filter: ((cliententi5_1_.datefinvalidite IS NULL) AND ((coalescelower(cliententi5_1_.abrege))::text ~~ '%%%'::text))
11. 1.172 58.297 ↓ 3.3 596 1

Nested Loop Left Join (cost=1.43..5,786.80 rows=180 width=83) (actual time=0.970..58.297 rows=596 loops=1)

12. 1.088 53.549 ↓ 3.3 596 1

Nested Loop Left Join (cost=1.43..5,736.33 rows=180 width=84) (actual time=0.936..53.549 rows=596 loops=1)

  • Filter: (commandeve3_2_.datefinvalidite IS NULL)
  • Rows Removed by Filter: 7
13. 1.440 42.813 ↓ 3.4 603 1

Nested Loop Left Join (cost=1.43..4,687.01 rows=180 width=84) (actual time=0.905..42.813 rows=603 loops=1)

14. 11.041 33.534 ↓ 3.4 603 1

Hash Left Join (cost=1.43..3,874.55 rows=180 width=51) (actual time=0.883..33.534 rows=603 loops=1)

  • Hash Cond: ((commandeve3_.id_typecommande)::text = (typecomman4_.id)::text)
  • Filter: ((typecomman4_.datefinvalidite IS NULL) AND ((coalescelower(typecomman4_.code))::text = 'sbg-rs'::text))
  • Rows Removed by Filter: 2749
15. 22.480 22.480 ↑ 1.0 3,352 1

Seq Scan on dm2_commandevente commandeve3_ (cost=0.00..2,963.35 rows=3,417 width=84) (actual time=0.006..22.480 rows=3,352 loops=1)

  • Filter: ((etatlivraison)::text <> '2'::text)
  • Rows Removed by Filter: 66818
16. 0.005 0.013 ↑ 1.0 19 1

Hash (cost=1.19..1.19 rows=19 width=46) (actual time=0.013..0.013 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
17. 0.008 0.008 ↑ 1.0 19 1

Seq Scan on dm1_typecommande typecomman4_ (cost=0.00..1.19 rows=19 width=46) (actual time=0.002..0.008 rows=19 loops=1)

18. 7.839 7.839 ↑ 1.0 1 603

Index Scan using dm2_documentcommercialvente_pkey on dm2_documentcommercialvente commandeve3_1_ (cost=0.00..4.50 rows=1 width=66) (actual time=0.013..0.013 rows=1 loops=603)

  • Index Cond: ((commandeve3_.id)::text = (id)::text)
19. 9.648 9.648 ↑ 1.0 1 603

Index Scan using dm1_documentcommercial_pkey on dm1_documentcommercial commandeve3_2_ (cost=0.00..5.82 rows=1 width=40) (actual time=0.015..0.016 rows=1 loops=603)

  • Index Cond: ((commandeve3_.id)::text = (id)::text)
20. 3.576 3.576 ↑ 1.0 1 596

Index Only Scan using dm2_client_pkey on dm2_client cliententi5_ (cost=0.00..0.27 rows=1 width=32) (actual time=0.005..0.006 rows=1 loops=596)

  • Index Cond: (id = (commandeve3_1_.id_client)::text)
  • Heap Fetches: 58
21. 4.768 4.768 ↑ 1.0 1 596

Index Scan using dm1_tiers_pkey on dm1_tiers cliententi5_1_ (cost=0.00..0.79 rows=1 width=53) (actual time=0.007..0.008 rows=1 loops=596)

  • Index Cond: ((cliententi5_.id)::text = (id)::text)
22. 7.152 7.152 ↑ 2.0 1 596

Index Scan using x_dm2_lignecommandevente_00 on dm2_lignecommandevente lignecomma1_ (cost=0.00..3.81 rows=2 width=70) (actual time=0.010..0.012 rows=1 loops=596)

  • Index Cond: ((id_commandevente)::text = (commandeve3_.id)::text)
23. 10.892 10.892 ↑ 1.0 1 778

Index Scan using specif_lignecommandeventespecif_id_lignecommandevente_key on specif_lignecommandeventespecif lignecomma0_ (cost=0.00..0.53 rows=1 width=83) (actual time=0.013..0.014 rows=1 loops=778)

  • Index Cond: ((id_lignecommandevente)::text = (lignecomma1_.id)::text)
  • Filter: ((datefinvalidite IS NULL) AND ((id)::text !~~ 'DefaultRecord_%'::text))
  • Rows Removed by Filter: 0
24. 6.260 6.260 ↑ 1.0 1 626

Index Scan using dm1_lignedocumentcommercial_pkey on dm1_lignedocumentcommercial lignecomma1_2_ (cost=0.00..1.64 rows=1 width=195) (actual time=0.009..0.010 rows=1 loops=626)

  • Index Cond: ((lignecomma1_.id)::text = (id)::text)
25. 5.436 5.436 ↑ 1.0 1 604

Index Scan using dm2_lignedocumentcommercialvente_pkey on dm2_lignedocumentcommercialvente lignecomma1_1_ (cost=0.00..0.87 rows=1 width=71) (actual time=0.008..0.009 rows=1 loops=604)

  • Index Cond: ((lignecomma1_.id)::text = (id)::text)
26. 4.228 4.228 ↑ 1.0 1 604

Index Scan using dm1_article_pkey on dm1_article articleent2_ (cost=0.00..0.27 rows=1 width=47) (actual time=0.005..0.007 rows=1 loops=604)

  • Index Cond: ((lignecomma1_2_.id_article)::text = (id)::text)
27. 1.804 1.812 ↑ 1.0 15 604

Materialize (cost=0.00..1.22 rows=15 width=46) (actual time=0.000..0.003 rows=15 loops=604)

28. 0.008 0.008 ↑ 1.0 15 1

Seq Scan on dm1_unite uniteentit6_ (cost=0.00..1.15 rows=15 width=46) (actual time=0.004..0.008 rows=15 loops=1)

29.          

SubPlan (for Nested Loop Left Join)

30. 0.000 95,938.756 ↑ 1.0 1 604

Nested Loop (cost=33,367.12..33,376.85 rows=1 width=8) (actual time=158.831..158.839 rows=1 loops=604)

31.          

Initplan (for Nested Loop)

32. 3.020 95,888.020 ↑ 481.0 1 604

Nested Loop Left Join (cost=0.00..33,358.82 rows=481 width=32) (actual time=131.490..158.755 rows=1 loops=604)

33. 5.436 95,878.356 ↑ 481.0 1 604

Nested Loop Left Join (cost=0.00..33,192.19 rows=481 width=33) (actual time=131.475..158.739 rows=1 loops=604)

34. 95,860.236 95,860.236 ↑ 481.0 1 604

Seq Scan on specif_lignecommandeventespecif lignecomma10_ (cost=0.00..29,760.36 rows=481 width=33) (actual time=131.446..158.709 rows=1 loops=604)

  • Filter: ((coalescelower(id))::text ~~ lower((lignecomma0_.id)::text))
  • Rows Removed by Filter: 96945
35. 12.684 12.684 ↑ 1.0 1 604

Index Scan using dm2_lignecommandevente_pkey on dm2_lignecommandevente lignecomma11_ (cost=0.00..7.12 rows=1 width=66) (actual time=0.021..0.021 rows=1 loops=604)

  • Index Cond: ((lignecomma10_.id_lignecommandevente)::text = (id)::text)
36. 6.644 6.644 ↑ 1.0 1 604

Index Only Scan using dm2_commandevente_pkey on dm2_commandevente commandeve12_ (cost=0.00..0.34 rows=1 width=32) (actual time=0.011..0.011 rows=1 loops=604)

  • Index Cond: (id = (lignecomma11_.id_commandevente)::text)
  • Heap Fetches: 291
37. 13.892 95,928.488 ↑ 1.0 1 604

Hash Right Join (cost=8.30..9.72 rows=1 width=41) (actual time=158.814..158.822 rows=1 loops=604)

  • Hash Cond: ((tourneesup8_.id)::text = (commandeve7_.id_tourneesup)::text)
38. 3.020 3.020 ↑ 1.0 30 604

Seq Scan on specif_tourneesup tourneesup8_ (cost=0.00..1.30 rows=30 width=40) (actual time=0.002..0.005 rows=30 loops=604)

39. 95,895.268 95,911.576 ↑ 1.0 1 604

Hash (cost=8.29..8.29 rows=1 width=66) (actual time=158.794..158.794 rows=1 loops=604)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
40. 16.308 16.308 ↑ 1.0 1 604

Index Scan using specif_commandeventespecif_id_commandevente_key on specif_commandeventespecif commandeve7_ (cost=0.00..8.29 rows=1 width=66) (actual time=0.027..0.027 rows=1 loops=604)

  • Index Cond: ((id_commandevente)::text = ($3)::text)
41. 5.436 5.436 ↑ 1.0 1 604

Index Only Scan using dm2_commandevente_pkey on dm2_commandevente commandeve9_ (cost=0.00..8.29 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=604)

  • Index Cond: (id = ($3)::text)
  • Heap Fetches: 291
42. 1.812 45.904 ↑ 2.0 1 604

Nested Loop (cost=0.00..37.70 rows=2 width=8) (actual time=0.074..0.076 rows=1 loops=604)

43. 1.812 20.536 ↑ 1.0 1 604

Nested Loop (cost=0.00..25.19 rows=1 width=33) (actual time=0.032..0.034 rows=1 loops=604)

44. 2.416 13.288 ↑ 1.0 1 604

Nested Loop (cost=0.00..16.71 rows=1 width=33) (actual time=0.021..0.022 rows=1 loops=604)

45. 5.436 5.436 ↑ 1.0 1 604

Index Only Scan using dm2_lignecommandevente_pkey on dm2_lignecommandevente lignecomma13_ (cost=0.00..8.31 rows=1 width=33) (actual time=0.009..0.009 rows=1 loops=604)

  • Index Cond: (id = (lignecomma1_.id)::text)
  • Heap Fetches: 260
46. 5.436 5.436 ↑ 1.0 1 604

Index Only Scan using dm2_lignedocumentcommercialvente_pkey on dm2_lignedocumentcommercialvente lignecomma13_1_ (cost=0.00..8.39 rows=1 width=33) (actual time=0.009..0.009 rows=1 loops=604)

  • Index Cond: (id = (lignecomma1_.id)::text)
  • Heap Fetches: 93
47. 5.436 5.436 ↑ 1.0 1 604

Index Only Scan using dm1_lignedocumentcommercial_pkey on dm1_lignedocumentcommercial lignecomma13_2_ (cost=0.00..8.47 rows=1 width=33) (actual time=0.009..0.009 rows=1 loops=604)

  • Index Cond: (id = (lignecomma1_.id)::text)
  • Heap Fetches: 186
48. 23.556 23.556 ↑ 2.0 1 604

Index Scan using dm2_lignecommandevente_cs_pkey on dm2_lignecommandevente_cs attributsu14_ (cost=0.00..12.49 rows=2 width=41) (actual time=0.038..0.039 rows=1 loops=604)

  • Index Cond: (((id_lignecommandevente)::text = (lignecomma1_.id)::text) AND ((clef)::text = 'sup_LigneCommandeVente_date_arrivee_prevue'::text))
49. 1.812 15.704 ↑ 2.0 1 604

Nested Loop (cost=0.00..37.70 rows=2 width=8) (actual time=0.025..0.026 rows=1 loops=604)

50. 1.812 9.664 ↑ 1.0 1 604

Nested Loop (cost=0.00..25.19 rows=1 width=33) (actual time=0.015..0.016 rows=1 loops=604)

51. 1.208 6.040 ↑ 1.0 1 604

Nested Loop (cost=0.00..16.71 rows=1 width=33) (actual time=0.010..0.010 rows=1 loops=604)

52. 2.416 2.416 ↑ 1.0 1 604

Index Only Scan using dm2_lignecommandevente_pkey on dm2_lignecommandevente lignecomma15_ (cost=0.00..8.31 rows=1 width=33) (actual time=0.004..0.004 rows=1 loops=604)

  • Index Cond: (id = (lignecomma1_.id)::text)
  • Heap Fetches: 260
53. 2.416 2.416 ↑ 1.0 1 604

Index Only Scan using dm2_lignedocumentcommercialvente_pkey on dm2_lignedocumentcommercialvente lignecomma15_1_ (cost=0.00..8.39 rows=1 width=33) (actual time=0.004..0.004 rows=1 loops=604)

  • Index Cond: (id = (lignecomma1_.id)::text)
  • Heap Fetches: 93
54. 1.812 1.812 ↑ 1.0 1 604

Index Only Scan using dm1_lignedocumentcommercial_pkey on dm1_lignedocumentcommercial lignecomma15_2_ (cost=0.00..8.47 rows=1 width=33) (actual time=0.003..0.003 rows=1 loops=604)

  • Index Cond: (id = (lignecomma1_.id)::text)
  • Heap Fetches: 186
55. 4.228 4.228 ↑ 2.0 1 604

Index Scan using dm2_lignecommandevente_cs_pkey on dm2_lignecommandevente_cs attributsu16_ (cost=0.00..12.49 rows=2 width=41) (actual time=0.007..0.007 rows=1 loops=604)

  • Index Cond: (((id_lignecommandevente)::text = (lignecomma1_.id)::text) AND ((clef)::text = 'sup_LigneCommandeVente_heure_arrivee_prevue'::text))
Total runtime : 96,125.563 ms