explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 54jf : Optimization for: plan #wvH5

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.002 21,339.233 ↓ 0.0 0 1

Limit (cost=147,829.58..147,829.62 rows=18 width=149) (actual time=21,339.233..21,339.233 rows=0 loops=1)

2. 0.007 21,339.231 ↓ 0.0 0 1

Sort (cost=147,829.58..147,829.62 rows=18 width=149) (actual time=21,339.231..21,339.231 rows=0 loops=1)

  • Sort Key: (ltrim((gtindoublon.gtin_code)::text, '0'::text)), article.article_id
  • Sort Method: quicksort Memory: 25kB
3. 4.102 21,339.224 ↓ 0.0 0 1

Nested Loop (cost=130,013.57..147,829.20 rows=18 width=149) (actual time=21,339.224..21,339.224 rows=0 loops=1)

  • Join Filter: (ltrim((gtindoublon.gtin_code)::text, '0'::text) = (ltrim((gtin_2.gtin_code)::text, '0'::text)))
4. 3.162 875.595 ↓ 3,883.0 3,883 1

Nested Loop (cost=123,103.52..140,757.74 rows=1 width=130) (actual time=784.102..875.595 rows=3,883 loops=1)

5. 3.010 849.225 ↓ 107.4 3,868 1

Nested Loop Left Join (cost=123,103.10..140,568.63 rows=36 width=121) (actual time=784.076..849.225 rows=3,868 loops=1)

  • Join Filter: ((gtin2.gtin_dossier_id = article.article_dossier_id) AND (gtin2.gtin_article_id = article.article_id))
  • Rows Removed by Join Filter: 7734
6. 6.960 471.019 ↓ 107.4 3,868 1

Nested Loop Left Join (cost=62,089.25..78,556.27 rows=36 width=108) (actual time=409.380..471.019 rows=3,868 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: 46404
7. 16.568 54.051 ↓ 107.4 3,868 1

Gather (cost=1,075.39..16,543.91 rows=36 width=95) (actual time=2.554..54.051 rows=3,868 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 3.543 37.483 ↓ 85.9 1,289 3

Nested Loop Left Join (cost=75.39..15,540.31 rows=15 width=95) (actual time=0.502..37.483 rows=1,289 loops=3)

9. 5.561 33.938 ↓ 85.9 1,289 3

Nested Loop Left Join (cost=75.11..15,535.16 rows=15 width=88) (actual time=0.470..33.938 rows=1,289 loops=3)

  • Join Filter: (condachat.condachat_id = refachat.refachat_condachat_id)
  • Rows Removed by Join Filter: 6
10. 8.233 28.374 ↓ 85.9 1,289 3

Nested Loop Left Join (cost=74.69..15,487.64 rows=15 width=80) (actual time=0.435..28.374 rows=1,289 loops=3)

  • Join Filter: (condachat.condachat_dossier_id = produit.produit_dossier_id)
11. 7.424 20.136 ↓ 85.9 1,289 3

Nested Loop Left Join (cost=74.27..15,434.99 rows=15 width=80) (actual time=0.397..20.136 rows=1,289 loops=3)

12. 9.843 12.707 ↓ 85.9 1,289 3

Nested Loop (cost=73.85..15,348.22 rows=15 width=51) (actual time=0.353..12.707 rows=1,289 loops=3)

13. 2.165 2.858 ↑ 1.2 1,289 3

Parallel Bitmap Heap Scan on article (cost=73.43..7,389.14 rows=1,559 width=47) (actual time=0.309..2.858 rows=1,289 loops=3)

  • Recheck Cond: (article_dossier_id = 85)
  • Filter: ((article_etat IS NOT NULL) AND (article_type IS NOT NULL))
  • Heap Blocks: exact=1
14. 0.693 0.693 ↓ 1.0 3,914 1

Bitmap Index Scan on article_produit_sk (cost=0.00..72.49 rows=3,742 width=0) (actual time=0.692..0.693 rows=3,914 loops=1)

  • Index Cond: (article_dossier_id = 85)
15. 0.006 0.006 ↑ 1.0 1 3,868

Index Scan using produit_pk on produit (cost=0.42..5.11 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=3,868)

  • Index Cond: (produit_id = article.article_produit_id)
  • Filter: (produit_dossier_id = 85)
16. 0.005 0.005 ↑ 1.0 1 3,868

Index Scan using articleden_ak on articleden (cost=0.42..5.78 rows=1 width=33) (actual time=0.005..0.005 rows=1 loops=3,868)

  • Index Cond: ((article.article_id = articleden_article_id) AND (articleden_dossier_id = 85))
17. 0.005 0.005 ↑ 1.0 1 3,868

Index Scan using condachat_ak on condachat (cost=0.42..3.50 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=3,868)

  • Index Cond: ((condachat_dossier_id = 85) AND (condachat_produit_id = article.article_produit_id))
  • Filter: condachat_principal
  • Rows Removed by Filter: 0
18. 0.003 0.003 ↑ 1.0 1 3,868

Index Scan using refachat_ak on refachat (cost=0.42..3.16 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=3,868)

  • Index Cond: (refachat_article_id = article.article_id)
  • Filter: ((refachat_dossier_id = 85) AND (refachat_dossier_id = article.article_dossier_id))
19. 0.002 0.002 ↑ 1.0 1 3,868

Index Scan using fournisseur_pk on fournisseur (cost=0.28..0.34 rows=1 width=11) (actual time=0.002..0.002 rows=1 loops=3,868)

  • Index Cond: (condachat.condachat_fournisseur_id = fournisseur_id)
20. 3.189 410.008 ↓ 6.0 12 3,868

Materialize (cost=61,013.86..62,011.10 rows=2 width=21) (actual time=0.105..0.106 rows=12 loops=3,868)

21. 0.705 406.819 ↓ 6.0 12 1

Subquery Scan on gtin1 (cost=61,013.86..62,011.09 rows=2 width=21) (actual time=406.570..406.819 rows=12 loops=1)

  • Filter: ((gtin1.index = 1) AND (gtin1.gtin_dossier_id = 85))
  • Rows Removed by Filter: 9495
22. 2.684 406.114 ↑ 6.0 9,507 1

Sort (cost=61,013.86..61,156.32 rows=56,985 width=33) (actual time=405.782..406.114 rows=9,507 loops=1)

  • Sort Key: gtin.gtin_id
  • Sort Method: quicksort Memory: 1127kB
23. 4.160 403.430 ↑ 6.0 9,507 1

WindowAgg (cost=55,372.83..56,512.53 rows=56,985 width=33) (actual time=398.700..403.430 rows=9,507 loops=1)

24. 3.530 399.270 ↑ 6.0 9,507 1

Sort (cost=55,372.83..55,515.29 rows=56,985 width=25) (actual time=398.693..399.270 rows=9,507 loops=1)

  • Sort Key: gtin.gtin_article_id, gtin.gtin_id
  • Sort Method: quicksort Memory: 1127kB
25. 19.817 395.740 ↑ 6.0 9,507 1

Gather (cost=27,406.90..50,871.50 rows=56,985 width=25) (actual time=216.192..395.740 rows=9,507 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
26. 126.180 375.923 ↑ 7.5 3,169 3

Hash Join (cost=26,406.90..44,173.00 rows=23,744 width=25) (actual time=219.644..375.923 rows=3,169 loops=3)

  • Hash Cond: ((gtin.gtin_article_id = article_1.article_id) AND (gtin.gtin_dossier_id = article_1.article_dossier_id))
  • Join Filter: ((gtin.gtin_code)::text <> (article_1.article_gtin)::text)
  • Rows Removed by Join Filter: 169745
27. 31.520 31.520 ↑ 1.3 172,914 3

Parallel Seq Scan on gtin (cost=0.00..10,540.90 rows=216,990 width=25) (actual time=0.017..31.520 rows=172,914 loops=3)

28. 115.073 218.223 ↑ 1.0 509,235 3

Hash (cost=15,308.96..15,308.96 rows=531,996 width=21) (actual time=218.223..218.223 rows=509,235 loops=3)

  • Buckets: 262144 Batches: 4 Memory Usage: 8964kB
29. 103.150 103.150 ↑ 1.0 509,235 3

Seq Scan on article article_1 (cost=0.00..15,308.96 rows=531,996 width=21) (actual time=0.017..103.150 rows=509,235 loops=3)

30. 0.504 375.196 ↑ 1.0 2 3,868

Materialize (cost=61,013.86..62,011.10 rows=2 width=21) (actual time=0.097..0.097 rows=2 loops=3,868)

31. 0.643 374.692 ↑ 1.0 2 1

Subquery Scan on gtin2 (cost=61,013.86..62,011.09 rows=2 width=21) (actual time=374.691..374.692 rows=2 loops=1)

  • Filter: ((gtin2.index = 2) AND (gtin2.gtin_dossier_id = 85))
  • Rows Removed by Filter: 9505
32. 2.735 374.049 ↑ 6.0 9,507 1

Sort (cost=61,013.86..61,156.32 rows=56,985 width=33) (actual time=373.712..374.049 rows=9,507 loops=1)

  • Sort Key: gtin_1.gtin_id
  • Sort Method: quicksort Memory: 1127kB
33. 4.243 371.314 ↑ 6.0 9,507 1

WindowAgg (cost=55,372.83..56,512.53 rows=56,985 width=33) (actual time=366.460..371.314 rows=9,507 loops=1)

34. 3.626 367.071 ↑ 6.0 9,507 1

Sort (cost=55,372.83..55,515.29 rows=56,985 width=25) (actual time=366.452..367.071 rows=9,507 loops=1)

  • Sort Key: gtin_1.gtin_article_id, gtin_1.gtin_id
  • Sort Method: quicksort Memory: 1127kB
35. 16.510 363.445 ↑ 6.0 9,507 1

Gather (cost=27,406.90..50,871.50 rows=56,985 width=25) (actual time=177.810..363.445 rows=9,507 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
36. 129.610 346.935 ↑ 7.5 3,169 3

Hash Join (cost=26,406.90..44,173.00 rows=23,744 width=25) (actual time=187.182..346.935 rows=3,169 loops=3)

  • Hash Cond: ((gtin_1.gtin_article_id = article_2.article_id) AND (gtin_1.gtin_dossier_id = article_2.article_dossier_id))
  • Join Filter: ((gtin_1.gtin_code)::text <> (article_2.article_gtin)::text)
  • Rows Removed by Join Filter: 169745
37. 31.359 31.359 ↑ 1.3 172,914 3

Parallel Seq Scan on gtin gtin_1 (cost=0.00..10,540.90 rows=216,990 width=25) (actual time=0.012..31.359 rows=172,914 loops=3)

38. 96.674 185.966 ↑ 1.0 509,235 3

Hash (cost=15,308.96..15,308.96 rows=531,996 width=21) (actual time=185.966..185.966 rows=509,235 loops=3)

  • Buckets: 262144 Batches: 4 Memory Usage: 8964kB
39. 89.292 89.292 ↑ 1.0 509,235 3

Seq Scan on article article_2 (cost=0.00..15,308.96 rows=531,996 width=21) (actual time=0.014..89.292 rows=509,235 loops=3)

40. 23.208 23.208 ↑ 1.0 1 3,868

Index Scan using gtin_article_sk on gtin gtindoublon (cost=0.42..5.24 rows=1 width=21) (actual time=0.005..0.006 rows=1 loops=3,868)

  • Index Cond: ((gtin_dossier_id = 85) AND (gtin_article_id = article.article_id))
41. 19,706.225 20,459.527 ↓ 0.0 0 3,883

GroupAggregate (cost=6,910.05..6,981.89 rows=3,581 width=32) (actual time=5.269..5.269 rows=0 loops=3,883)

  • Group Key: (ltrim((gtin_2.gtin_code)::text, '0'::text))
  • Filter: (count(DISTINCT gtin_2.gtin_article_id) > 1)
  • Rows Removed by Filter: 3883
42. 751.443 753.302 ↓ 1.1 3,883 3,883

Sort (cost=6,910.05..6,919.07 rows=3,611 width=36) (actual time=0.004..0.194 rows=3,883 loops=3,883)

  • Sort Key: (ltrim((gtin_2.gtin_code)::text, '0'::text))
  • Sort Method: quicksort Memory: 400kB
43. 1.708 1.859 ↓ 1.1 3,883 1

Bitmap Heap Scan on gtin gtin_2 (cost=68.41..6,696.67 rows=3,611 width=36) (actual time=0.191..1.859 rows=3,883 loops=1)

  • Recheck Cond: (gtin_dossier_id = 85)
  • Heap Blocks: exact=394
44. 0.151 0.151 ↓ 1.1 3,891 1

Bitmap Index Scan on gtin_article_sk (cost=0.00..67.50 rows=3,611 width=0) (actual time=0.151..0.151 rows=3,891 loops=1)

  • Index Cond: (gtin_dossier_id = 85)