explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7mlB

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 10,266.260 ↓ 13.0 13 1

Limit (cost=353,464.32..353,464.32 rows=1 width=203) (actual time=10,266.259..10,266.260 rows=13 loops=1)

2. 0.021 10,266.259 ↓ 13.0 13 1

Sort (cost=353,464.32..353,464.32 rows=1 width=203) (actual time=10,266.258..10,266.259 rows=13 loops=1)

  • Sort Key: article.article_ecommerce DESC, article.article_id
  • Sort Method: quicksort Memory: 28kB
3. 4.441 10,266.238 ↓ 13.0 13 1

Nested Loop Left Join (cost=136,357.88..353,464.31 rows=1 width=203) (actual time=8,319.325..10,266.238 rows=13 loops=1)

  • Join Filter: ((gtin1.gtin_dossier_id = article.article_dossier_id) AND (gtin1.gtin_article_id = article.article_id))
  • Rows Removed by Join Filter: 105,937
4. 0.006 10,229.791 ↓ 13.0 13 1

Nested Loop Left Join (cost=71,049.96..288,155.37 rows=1 width=194) (actual time=8,298.078..10,229.791 rows=13 loops=1)

  • Join Filter: (_actuel_profilachatprofiltaxe18__.profiltaxe_dossier_id = article.article_dossier_id)
5. 0.000 10,229.759 ↓ 13.0 13 1

Nested Loop (cost=71,049.68..288,155.05 rows=1 width=180) (actual time=8,298.072..10,229.759 rows=13 loops=1)

6. 0.691 10,201.884 ↓ 9,536.0 9,536 1

Nested Loop Left Join (cost=71,049.40..288,154.64 rows=1 width=177) (actual time=180.912..10,201.884 rows=9,536 loops=1)

  • Join Filter: (_actuel_profilachatrefachat18__.refachat_dossier_id = article.article_dossier_id)
7. 5.653 10,182.121 ↓ 9,536.0 9,536 1

Nested Loop Left Join (cost=71,048.97..288,146.18 rows=1 width=168) (actual time=180.905..10,182.121 rows=9,536 loops=1)

  • Join Filter: (condachat.condachat_id = refachat.refachat_condachat_id)
  • Rows Removed by Join Filter: 635
8. 1.225 10,157.396 ↓ 9,536.0 9,536 1

Nested Loop Left Join (cost=71,048.54..288,139.96 rows=1 width=167) (actual time=180.898..10,157.396 rows=9,536 loops=1)

9. 6,222.199 10,127.563 ↓ 9,536.0 9,536 1

Nested Loop (cost=71,048.11..288,133.41 rows=1 width=167) (actual time=180.891..10,127.563 rows=9,536 loops=1)

  • Join Filter: (_actuel_profilachat18__.tarifachatactuel_article_id = article.article_id)
  • Rows Removed by Join Filter: 124,913,934
10. 10.225 246.884 ↑ 1.1 9,730 1

Nested Loop Left Join (cost=70,799.63..263,583.55 rows=10,841 width=143) (actual time=160.641..246.884 rows=9,730 loops=1)

11. 5.697 217.199 ↑ 1.1 9,730 1

Hash Left Join (cost=70,799.20..183,378.75 rows=10,841 width=84) (actual time=160.631..217.199 rows=9,730 loops=1)

  • Hash Cond: ((article.article_dossier_id = tarifachatactuel.tarifachatactuel_dossier_id) AND (article.article_id = tarifachatactuel.tarifachatactuel_article_id))
12. 4.779 197.717 ↑ 1.1 9,730 1

Hash Left Join (cost=46,614.02..157,919.75 rows=10,841 width=72) (actual time=146.839..197.717 rows=9,730 loops=1)

  • Hash Cond: (produit.produit_mesure_id = mesure.mesure_id)
13. 7.967 177.689 ↑ 1.1 9,730 1

Nested Loop (cost=43,720.37..154,983.27 rows=10,841 width=68) (actual time=131.566..177.689 rows=9,730 loops=1)

14. 6.560 150.262 ↑ 1.1 9,730 1

Hash Right Join (cost=43,719.94..80,230.03 rows=10,841 width=48) (actual time=131.556..150.262 rows=9,730 loops=1)

  • Hash Cond: ((tarifventeactuel.tarifventeactuel_dossier_id = article.article_dossier_id) AND (tarifventeactuel.tarifventeactuel_article_id = article.article_id))
15. 12.661 13.311 ↓ 397.9 13,131 1

Bitmap Heap Scan on tarifventeactuel (cost=450.29..36,960.12 rows=33 width=16) (actual time=1.156..13.311 rows=13,131 loops=1)

  • Recheck Cond: (tarifventeactuel_dossier_id = 205)
  • Filter: ((tarifventeactuel_profiltarif_id IS NULL) AND (tarifventeactuel_etablissement_id = 344))
  • Heap Blocks: exact=5,635
16. 0.650 0.650 ↓ 1.1 13,612 1

Bitmap Index Scan on tarifventeactuel_dossier_sk (cost=0.00..450.28 rows=12,513 width=0) (actual time=0.650..0.650 rows=13,612 loops=1)

  • Index Cond: (tarifventeactuel_dossier_id = 205)
17. 1.465 130.391 ↑ 1.1 9,730 1

Hash (cost=43,107.04..43,107.04 rows=10,841 width=40) (actual time=130.391..130.391 rows=9,730 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 821kB
18. 4.333 128.926 ↑ 1.1 9,730 1

Hash Left Join (cost=12,490.54..43,107.04 rows=10,841 width=40) (actual time=122.045..128.926 rows=9,730 loops=1)

  • Hash Cond: (article.article_articleex_id = _article_articleex__.articleex_id)
  • Filter: ((_article_articleex__.articleex_enum1 <> 3) OR (_article_articleex__.articleex_enum1 IS NULL))
  • Rows Removed by Filter: 3,371
19. 2.921 3.728 ↓ 1.1 13,101 1

Bitmap Heap Scan on article (cost=368.39..30,807.87 rows=11,703 width=36) (actual time=1.018..3.728 rows=13,101 loops=1)

  • Recheck Cond: ((article_dossier_id = 205) AND (article_etat = 0))
  • Heap Blocks: exact=1,847
20. 0.807 0.807 ↓ 1.1 13,101 1

Bitmap Index Scan on article_etat_sk (cost=0.00..365.46 rows=11,703 width=0) (actual time=0.807..0.807 rows=13,101 loops=1)

  • Index Cond: ((article_dossier_id = 205) AND (article_etat = 0))
21. 45.395 120.865 ↓ 1.0 324,376 1

Hash (cost=8,068.18..8,068.18 rows=324,318 width=8) (actual time=120.865..120.865 rows=324,376 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 15,599kB
22. 75.470 75.470 ↓ 1.0 324,376 1

Seq Scan on articleex _article_articleex__ (cost=0.00..8,068.18 rows=324,318 width=8) (actual time=0.009..75.470 rows=324,376 loops=1)

23. 19.460 19.460 ↑ 1.0 1 9,730

Index Scan using produit_pk on produit (cost=0.43..6.90 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=9,730)

  • Index Cond: (produit_id = article.article_produit_id)
24. 5.112 15.249 ↓ 1.0 59,491 1

Hash (cost=2,153.82..2,153.82 rows=59,187 width=12) (actual time=15.249..15.249 rows=59,491 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,657kB
25. 6.553 10.137 ↓ 1.0 59,491 1

Hash Left Join (cost=81.74..2,153.82 rows=59,187 width=12) (actual time=0.534..10.137 rows=59,491 loops=1)

  • Hash Cond: (mesure.mesure_unitecontenance_id = _mesure_unitecontenance__.unite_id)
26. 3.058 3.058 ↓ 1.0 59,491 1

Seq Scan on mesure (cost=0.00..1,324.87 rows=59,187 width=8) (actual time=0.005..3.058 rows=59,491 loops=1)

27. 0.207 0.526 ↓ 1.0 2,307 1

Hash (cost=53.55..53.55 rows=2,255 width=8) (actual time=0.526..0.526 rows=2,307 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 123kB
28. 0.319 0.319 ↓ 1.0 2,307 1

Seq Scan on unite _mesure_unitecontenance__ (cost=0.00..53.55 rows=2,255 width=8) (actual time=0.003..0.319 rows=2,307 loops=1)

29. 1.519 13.785 ↓ 291.8 13,131 1

Hash (cost=24,184.50..24,184.50 rows=45 width=20) (actual time=13.785..13.785 rows=13,131 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 847kB
30. 11.137 12.266 ↓ 291.8 13,131 1

Bitmap Heap Scan on tarifachatactuel (cost=248.06..24,184.50 rows=45 width=20) (actual time=1.647..12.266 rows=13,131 loops=1)

  • Recheck Cond: (tarifachatactuel_dossier_id = 205)
  • Filter: ((tarifachatactuel_profiltarifachat_id IS NULL) AND tarifachatactuel_principal AND (tarifachatactuel_etablissement_id = 344))
  • Rows Removed by Filter: 14,097
  • Heap Blocks: exact=5,385
31. 1.129 1.129 ↓ 3.7 27,228 1

Bitmap Index Scan on tarifachatactuel_dossier_sk (cost=0.00..248.05 rows=7,416 width=0) (actual time=1.129..1.129 rows=27,228 loops=1)

  • Index Cond: (tarifachatactuel_dossier_id = 205)
32. 19.460 19.460 ↑ 1.0 1 9,730

Index Scan using articleden_ak on articleden (cost=0.43..7.39 rows=1 width=63) (actual time=0.002..0.002 rows=1 loops=9,730)

  • Index Cond: (article.article_id = articleden_article_id)
33. 3,627.788 3,658.480 ↓ 12,839.0 12,839 9,730

Materialize (cost=248.48..24,387.24 rows=1 width=36) (actual time=0.000..0.376 rows=12,839 loops=9,730)

34. 5.254 30.692 ↓ 12,839.0 12,839 1

Nested Loop (cost=248.48..24,387.24 rows=1 width=36) (actual time=1.666..30.692 rows=12,839 loops=1)

35. 11.137 12.307 ↓ 547.1 13,131 1

Bitmap Heap Scan on tarifachatactuel _actuel_profilachat18__ (cost=248.06..24,184.50 rows=24 width=28) (actual time=1.658..12.307 rows=13,131 loops=1)

  • Recheck Cond: (tarifachatactuel_dossier_id = 205)
  • Filter: (tarifachatactuel_principal AND (tarifachatactuel_profiltarifachat_id = 18))
  • Rows Removed by Filter: 14,097
  • Heap Blocks: exact=5,385
36. 1.170 1.170 ↓ 3.7 27,228 1

Bitmap Index Scan on tarifachatactuel_dossier_sk (cost=0.00..248.05 rows=7,416 width=0) (actual time=1.170..1.170 rows=27,228 loops=1)

  • Index Cond: (tarifachatactuel_dossier_id = 205)
37. 13.131 13.131 ↑ 1.0 1 13,131

Index Scan using condachat_pk on condachat _actuel_profilachatcondachat18__ (cost=0.43..8.45 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=13,131)

  • Index Cond: (condachat_id = _actuel_profilachat18__.tarifachatactuel_condachat_id)
  • Filter: (condachat_dossier_id = 205)
38. 28.608 28.608 ↑ 1.0 1 9,536

Index Scan using condachat_ak on condachat (cost=0.43..6.54 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=9,536)

  • Index Cond: ((condachat_dossier_id = produit.produit_dossier_id) AND (condachat_produit_id = article.article_produit_id))
  • Filter: condachat_principal
  • Rows Removed by Filter: 0
39. 19.072 19.072 ↑ 1.0 1 9,536

Index Scan using refachat_ak on refachat (cost=0.43..6.20 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=9,536)

  • Index Cond: (refachat_article_id = article.article_id)
  • Filter: ((refachat_dossier_id = 205) AND (refachat_dossier_id = article.article_dossier_id))
40. 19.072 19.072 ↑ 1.0 1 9,536

Index Scan using refachat_pk on refachat _actuel_profilachatrefachat18__ (cost=0.43..8.45 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=9,536)

  • Index Cond: (_actuel_profilachat18__.tarifachatactuel_refachat_id = refachat_id)
  • Filter: (refachat_dossier_id = 205)
41. 28.608 28.608 ↓ 0.0 0 9,536

Index Scan using fournisseur_pk on fournisseur _actuel_profilachatfournisseur18__ (cost=0.29..0.36 rows=1 width=15) (actual time=0.003..0.003 rows=0 loops=9,536)

  • Index Cond: (fournisseur_id = _actuel_profilachatcondachat18__.condachat_fournisseur_id)
  • Filter: ((fournisseur_dossier_id = 205) AND (lower(unaccent((fournisseur_code)::text)) ~~ 'naja%'::text))
  • Rows Removed by Filter: 1
42. 0.026 0.026 ↑ 1.0 1 13

Index Scan using profiltaxe_pk on profiltaxe _actuel_profilachatprofiltaxe18__ (cost=0.28..0.30 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=13)

  • Index Cond: (profiltaxe_id = _actuel_profilachatcondachat18__.condachat_profiltaxe_id)
  • Filter: (profiltaxe_dossier_id = 205)
43. 8.567 32.006 ↓ 8,150.0 8,150 13

Subquery Scan on gtin1 (cost=65,307.92..65,308.92 rows=1 width=21) (actual time=1.538..2.462 rows=8,150 loops=13)

  • Filter: ((gtin1.index = 1) AND (gtin1.gtin_dossier_id = 205))
  • Rows Removed by Filter: 1,013
44. 5.065 23.439 ↓ 160.8 9,163 13

Sort (cost=65,307.92..65,308.07 rows=57 width=33) (actual time=1.538..1.803 rows=9,163 loops=13)

  • Sort Key: gtin.gtin_id
  • Sort Method: quicksort Memory: 1,100kB
45. 2.651 18.374 ↓ 160.8 9,163 1

WindowAgg (cost=65,305.12..65,306.26 rows=57 width=33) (actual time=15.431..18.374 rows=9,163 loops=1)

46. 2.102 15.723 ↓ 160.8 9,163 1

Sort (cost=65,305.12..65,305.26 rows=57 width=25) (actual time=15.428..15.723 rows=9,163 loops=1)

  • Sort Key: gtin.gtin_article_id, gtin.gtin_id
  • Sort Method: quicksort Memory: 1,100kB
47. 4.178 13.621 ↓ 160.8 9,163 1

Hash Join (cost=33,431.31..65,303.46 rows=57 width=25) (actual time=6.018..13.621 rows=9,163 loops=1)

  • Hash Cond: (gtin.gtin_article_id = article_1.article_id)
  • Join Filter: ((gtin.gtin_code)::text <> (article_1.article_gtin)::text)
  • Rows Removed by Join Filter: 13,131
48. 3.619 4.390 ↓ 1.6 22,294 1

Bitmap Heap Scan on gtin (cost=341.18..32,008.90 rows=13,516 width=25) (actual time=0.955..4.390 rows=22,294 loops=1)

  • Recheck Cond: (gtin_dossier_id = 205)
  • Heap Blocks: exact=2,194
49. 0.771 0.771 ↓ 1.6 22,295 1

Bitmap Index Scan on gtin_article_sk (cost=0.00..337.80 rows=13,516 width=0) (actual time=0.771..0.771 rows=22,295 loops=1)

  • Index Cond: (gtin_dossier_id = 205)
50. 1.282 5.053 ↓ 1.0 13,131 1

Hash (cost=32,929.26..32,929.26 rows=12,869 width=21) (actual time=5.053..5.053 rows=13,131 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 847kB
51. 3.322 3.771 ↓ 1.0 13,131 1

Bitmap Heap Scan on article article_1 (cost=372.16..32,929.26 rows=12,869 width=21) (actual time=0.604..3.771 rows=13,131 loops=1)

  • Recheck Cond: (article_dossier_id = 205)
  • Heap Blocks: exact=1,855
52. 0.449 0.449 ↓ 1.0 13,131 1

Bitmap Index Scan on article_etat_sk (cost=0.00..368.95 rows=12,869 width=0) (actual time=0.449..0.449 rows=13,131 loops=1)

  • Index Cond: (article_dossier_id = 205)
Planning time : 15.166 ms