explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wvH5

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.001 19,249.574 ↓ 0.0 0 1

Limit (cost=147,834.54..147,834.58 rows=18 width=149) (actual time=19,249.574..19,249.574 rows=0 loops=1)

2. 0.005 19,249.573 ↓ 0.0 0 1

Sort (cost=147,834.54..147,834.58 rows=18 width=149) (actual time=19,249.573..19,249.573 rows=0 loops=1)

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

Nested Loop (cost=130,020.11..147,834.16 rows=18 width=149) (actual time=19,249.568..19,249.568 rows=0 loops=1)

  • Join Filter: (ltrim((gtindoublon.gtin_code)::text, '0'::text) = (ltrim((gtin_2.gtin_code)::text, '0'::text)))
4. 9.994 1,255.123 ↓ 3,882.0 3,882 1

Nested Loop (cost=123,103.52..140,757.74 rows=1 width=130) (actual time=1,174.666..1,255.123 rows=3,882 loops=1)

5. 4.120 1,218.060 ↓ 107.4 3,867 1

Nested Loop Left Join (cost=123,103.10..140,568.63 rows=36 width=121) (actual time=1,174.645..1,218.060 rows=3,867 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: 7732
6. 7.568 626.156 ↓ 107.4 3,867 1

Nested Loop Left Join (cost=62,089.25..78,556.27 rows=36 width=108) (actual time=590.147..626.156 rows=3,867 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: 46392
7. 0.000 26.937 ↓ 107.4 3,867 1

Gather (cost=1,075.39..16,543.91 rows=36 width=95) (actual time=4.600..26.937 rows=3,867 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 4.488 46.427 ↓ 85.9 1,289 3

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

9. 10.209 41.937 ↓ 85.9 1,289 3

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

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

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

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

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

12. 11.704 14.758 ↓ 85.9 1,289 3

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

13. 2.666 3.047 ↑ 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.170..3.047 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.381 0.381 ↓ 1.0 3,913 1

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

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

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

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

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

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

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

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

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

  • 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,867

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

  • Index Cond: (condachat.condachat_fournisseur_id = fournisseur_id)
20. 6.112 591.651 ↓ 6.0 12 3,867

Materialize (cost=61,013.86..62,011.10 rows=2 width=21) (actual time=0.152..0.153 rows=12 loops=3,867)

21. 0.751 585.539 ↓ 6.0 12 1

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

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

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

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

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

24. 4.067 573.224 ↑ 6.0 9,507 1

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

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

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

  • Workers Planned: 2
  • Workers Launched: 2
26. 189.573 532.788 ↑ 7.5 3,169 3

Hash Join (cost=26,406.90..44,173.00 rows=23,744 width=25) (actual time=310.236..532.788 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. 59.725 59.725 ↑ 1.3 172,914 3

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

28. 144.546 283.490 ↑ 1.0 509,234 3

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

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

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

30. 3.293 587.784 ↑ 1.0 2 3,867

Materialize (cost=61,013.86..62,011.10 rows=2 width=21) (actual time=0.151..0.152 rows=2 loops=3,867)

31. 0.694 584.491 ↑ 1.0 2 1

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

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

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

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

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

34. 4.096 576.350 ↑ 6.0 9,507 1

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

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

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

  • Workers Planned: 2
  • Workers Launched: 2
36. 159.383 489.598 ↑ 7.5 3,169 3

Hash Join (cost=26,406.90..44,173.00 rows=23,744 width=25) (actual time=288.722..489.598 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. 42.587 42.587 ↑ 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..42.587 rows=172,914 loops=3)

38. 154.269 287.628 ↑ 1.0 509,234 3

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

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

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

40. 27.069 27.069 ↑ 1.0 1 3,867

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

  • Index Cond: ((gtin_dossier_id = 85) AND (gtin_article_id = article.article_id))
41. 17,309.838 17,993.070 ↓ 0.0 0 3,882

GroupAggregate (cost=6,916.59..6,987.73 rows=3,546 width=32) (actual time=4.635..4.635 rows=0 loops=3,882)

  • Group Key: (ltrim((gtin_2.gtin_code)::text, '0'::text))
  • Filter: (count(DISTINCT gtin_2.gtin_article_id) > 1)
  • Rows Removed by Filter: 3878
42. 679.329 683.232 ↓ 1.1 3,878 3,882

Sort (cost=6,916.59..6,925.53 rows=3,575 width=36) (actual time=0.004..0.176 rows=3,878 loops=3,882)

  • Sort Key: (ltrim((gtin_2.gtin_code)::text, '0'::text))
  • Sort Method: quicksort Memory: 399kB
43. 3.735 3.903 ↓ 1.1 3,878 1

Bitmap Heap Scan on gtin gtin_2 (cost=68.40..6,705.60 rows=3,575 width=36) (actual time=0.213..3.903 rows=3,878 loops=1)

  • Recheck Cond: (gtin_dossier_id = 85)
  • Filter: ((gtin_code)::text ~ '^[0-9]+$'::text)
  • Rows Removed by Filter: 4
  • Heap Blocks: exact=394
44. 0.168 0.168 ↓ 1.1 3,890 1

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

  • Index Cond: (gtin_dossier_id = 85)