explain.depesz.com

PostgreSQL's explain analyze made readable

Result: du8q : Optimization for: Optimization for: Optimization for: plan #wvH5; plan #54jf; plan #JbMg

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.264 21,899.810 ↓ 2.3 30 1

Limit (cost=3,734,392.52..3,734,392.56 rows=13 width=148) (actual time=21,899.546..21,899.810 rows=30 loops=1)

2. 0.054 21,899.546 ↓ 2.3 30 1

Sort (cost=3,734,392.52..3,734,392.56 rows=13 width=148) (actual time=21,899.545..21,899.546 rows=30 loops=1)

  • Sort Key: (ltrim((gtin.gtin_code)::text, '0'::text)), article.article_id
  • Sort Method: quicksort Memory: 32kB
3. 0.038 21,899.492 ↓ 2.3 30 1

Nested Loop Left Join (cost=2,089,869.07..3,734,392.28 rows=13 width=148) (actual time=21,887.594..21,899.492 rows=30 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: 120
4. 1.446 11,644.164 ↓ 2.3 30 1

Nested Loop Left Join (cost=1,140,117.57..2,782,706.20 rows=13 width=120) (actual time=11,632.300..11,644.164 rows=30 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: 19620
5. 0.807 149.028 ↓ 2.3 30 1

Nested Loop Left Join (cost=190,366.07..1,831,020.15 rows=13 width=107) (actual time=139.301..149.028 rows=30 loops=1)

  • Join Filter: (condachat.condachat_fournisseur_id = fournisseur.fournisseur_id)
  • Rows Removed by Join Filter: 14262
6. 0.000 147.621 ↓ 2.3 30 1

Nested Loop Left Join (cost=190,366.07..1,830,881.53 rows=13 width=101) (actual time=139.149..147.621 rows=30 loops=1)

  • Join Filter: (condachat.condachat_id = refachat.refachat_condachat_id)
7. 0.000 147.647 ↓ 2.3 30 1

Gather (cost=190,365.65..1,830,874.71 rows=13 width=92) (actual time=139.140..147.647 rows=30 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 0.069 159.476 ↓ 2.0 10 3

Nested Loop Left Join (cost=189,365.65..1,829,873.41 rows=5 width=92) (actual time=131.717..159.476 rows=10 loops=3)

  • Join Filter: (condachat.condachat_dossier_id = produit.produit_dossier_id)
9. 0.081 159.400 ↓ 2.0 10 3

Nested Loop Left Join (cost=189,365.23..1,829,870.77 rows=5 width=92) (actual time=131.706..159.400 rows=10 loops=3)

10. 0.080 159.311 ↓ 2.0 10 3

Nested Loop (cost=189,364.79..1,829,846.96 rows=5 width=61) (actual time=131.691..159.311 rows=10 loops=3)

11. 0.104 159.223 ↑ 46.0 10 3

Nested Loop (cost=189,364.36..1,827,761.61 rows=460 width=57) (actual time=131.676..159.223 rows=10 loops=3)

12. 34.204 159.098 ↑ 3,938.6 5 3

Hash Join (cost=189,298.44..371,709.48 rows=19,693 width=21) (actual time=131.646..159.098 rows=5 loops=3)

  • Hash Cond: (('0'::text || (gtin2_1.gtin_code)::text) = (gtin.gtin_code)::text)
  • Join Filter: ((gtin.gtin_article_id <> gtin2_1.gtin_article_id) AND (gtin.gtin_id <> gtin2_1.gtin_id))
  • Rows Removed by Join Filter: 137
13. 5.316 10.824 ↑ 1.3 43,538 3

Parallel Bitmap Heap Scan on gtin gtin2_1 (cost=2,559.59..183,099.03 rows=56,944 width=25) (actual time=2.030..10.824 rows=43,538 loops=3)

  • Recheck Cond: (gtin_dossier_id = 15)
  • Heap Blocks: exact=508
14. 5.508 5.508 ↑ 1.0 130,620 1

Bitmap Index Scan on gtin_article_sk (cost=0.00..2,525.42 rows=136,665 width=0) (actual time=5.508..5.508 rows=130,620 loops=1)

  • Index Cond: (gtin_dossier_id = 15)
15. 45.938 114.070 ↑ 1.0 130,615 3

Hash (cost=184,095.54..184,095.54 rows=136,665 width=25) (actual time=114.070..114.070 rows=130,615 loops=3)

  • Buckets: 262144 Batches: 2 Memory Usage: 5861kB
16. 56.286 68.132 ↑ 1.0 130,615 3

Bitmap Heap Scan on gtin (cost=2,559.59..184,095.54 rows=136,665 width=25) (actual time=12.230..68.132 rows=130,615 loops=3)

  • Recheck Cond: (gtin_dossier_id = 15)
  • Heap Blocks: exact=3444
17. 11.846 11.846 ↑ 1.0 130,620 3

Bitmap Index Scan on gtin_article_sk (cost=0.00..2,525.42 rows=136,665 width=0) (actual time=11.846..11.846 rows=130,620 loops=3)

  • Index Cond: (gtin_dossier_id = 15)
18. 0.008 0.021 ↓ 2.0 2 15

Bitmap Heap Scan on article (cost=65.91..73.93 rows=1 width=44) (actual time=0.018..0.021 rows=2 loops=15)

  • Recheck Cond: ((article_id = gtin.gtin_article_id) OR (article_id = gtin2_1.gtin_article_id))
  • Filter: (article_dossier_id = 15)
  • Heap Blocks: exact=6
19. 0.001 0.013 ↓ 0.0 0 15

BitmapOr (cost=65.91..65.91 rows=2 width=0) (actual time=0.013..0.013 rows=0 loops=15)

20. 0.007 0.007 ↑ 1.0 1 15

Bitmap Index Scan on article_pk (cost=0.00..1.31 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=15)

  • Index Cond: (article_id = gtin.gtin_article_id)
21. 0.005 0.005 ↑ 1.0 1 15

Bitmap Index Scan on article_pk (cost=0.00..1.31 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=15)

  • Index Cond: (article_id = gtin2_1.gtin_article_id)
22. 0.008 0.008 ↑ 1.0 1 30

Index Scan using produit_pk on produit (cost=0.43..4.53 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=30)

  • Index Cond: (produit_id = article.article_produit_id)
  • Filter: (produit_dossier_id = 15)
23. 0.008 0.008 ↑ 1.0 1 30

Index Scan using articleden_ak on articleden (cost=0.43..4.76 rows=1 width=35) (actual time=0.008..0.008 rows=1 loops=30)

  • Index Cond: ((article.article_id = articleden_article_id) AND (articleden_dossier_id = 15))
24. 0.007 0.007 ↑ 1.0 1 30

Index Scan using condachat_ak on condachat (cost=0.42..0.52 rows=1 width=16) (actual time=0.006..0.007 rows=1 loops=30)

  • Index Cond: ((condachat_dossier_id = 15) AND (condachat_produit_id = article.article_produit_id))
  • Filter: condachat_principal
25. 0.120 0.120 ↑ 1.0 1 30

Index Scan using refachat_ak on refachat (cost=0.42..0.51 rows=1 width=25) (actual time=0.004..0.004 rows=1 loops=30)

  • Index Cond: (refachat_article_id = article.article_id)
  • Filter: ((refachat_dossier_id = 15) AND (refachat_dossier_id = article.article_dossier_id))
26. 0.523 0.600 ↑ 1.3 476 30

Materialize (cost=0.00..20.22 rows=615 width=10) (actual time=0.001..0.020 rows=476 loops=30)

27. 0.077 0.077 ↑ 1.2 519 1

Seq Scan on fournisseur (cost=0.00..17.15 rows=615 width=10) (actual time=0.006..0.077 rows=519 loops=1)

28. 0.830 11,493.690 ↓ 109.0 654 30

Materialize (cost=949,751.50..951,684.70 rows=6 width=21) (actual time=377.283..383.123 rows=654 loops=30)

29. 64.456 11,492.860 ↓ 109.0 654 1

Subquery Scan on gtin1 (cost=949,751.50..951,684.67 rows=6 width=21) (actual time=11,318.472..11,492.860 rows=654 loops=1)

  • Filter: ((gtin1.index = 1) AND (gtin1.gtin_dossier_id = 15))
  • Rows Removed by Filter: 766122
30. 614.354 11,428.404 ↓ 6.9 766,776 1

Sort (cost=949,751.50..950,027.67 rows=110,467 width=33) (actual time=11,318.470..11,428.404 rows=766,776 loops=1)

  • Sort Key: gtin_1.gtin_id
  • Sort Method: external sort Disk: 34512kB
31. 420.135 10,814.050 ↓ 6.9 766,776 1

WindowAgg (cost=938,288.75..940,498.09 rows=110,467 width=33) (actual time=10,242.437..10,814.050 rows=766,776 loops=1)

32. 510.730 10,393.915 ↓ 6.9 766,776 1

Sort (cost=938,288.75..938,564.92 rows=110,467 width=25) (actual time=10,242.430..10,393.915 rows=766,776 loops=1)

  • Sort Key: gtin_1.gtin_article_id, gtin_1.gtin_id
  • Sort Method: external merge Disk: 28568kB
33. 810.397 9,883.185 ↓ 6.9 766,776 1

Gather (cost=528,693.45..929,035.34 rows=110,467 width=25) (actual time=4,064.790..9,883.185 rows=766,776 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
34. 3,559.158 9,072.788 ↓ 5.6 255,592 3

Hash Join (cost=527,693.45..916,988.64 rows=46,028 width=25) (actual time=4,831.801..9,072.788 rows=255,592 loops=3)

  • Hash Cond: ((gtin_1.gtin_article_id = article_1.article_id) AND (gtin_1.gtin_dossier_id = article_1.article_dossier_id))
  • Join Filter: ((gtin_1.gtin_code)::text <> (article_1.article_gtin)::text)
  • Rows Removed by Join Filter: 3616173
35. 698.471 698.471 ↑ 1.2 3,871,765 3

Parallel Seq Scan on gtin gtin_1 (cost=0.00..234,156.23 rows=4,839,422 width=25) (actual time=0.012..698.471 rows=3,871,765 loops=3)

36. 2,533.744 4,815.159 ↑ 1.0 10,848,520 3

Hash (cost=301,391.38..301,391.38 rows=10,848,938 width=21) (actual time=4,815.159..4,815.159 rows=10,848,520 loops=3)

  • Buckets: 262144 Batches: 128 Memory Usage: 6645kB
37. 2,281.415 2,281.415 ↑ 1.0 10,848,520 3

Seq Scan on article article_1 (cost=0.00..301,391.38 rows=10,848,938 width=21) (actual time=0.022..2,281.415 rows=10,848,520 loops=3)

38. 0.008 10,255.290 ↑ 1.5 4 30

Materialize (cost=949,751.50..951,684.70 rows=6 width=21) (actual time=341.840..341.843 rows=4 loops=30)

39. 56.851 10,255.282 ↑ 1.5 4 1

Subquery Scan on gtin2 (cost=949,751.50..951,684.67 rows=6 width=21) (actual time=10,255.189..10,255.282 rows=4 loops=1)

  • Filter: ((gtin2.index = 2) AND (gtin2.gtin_dossier_id = 15))
  • Rows Removed by Filter: 766772
40. 585.306 10,198.431 ↓ 6.9 766,776 1

Sort (cost=949,751.50..950,027.67 rows=110,467 width=33) (actual time=10,093.462..10,198.431 rows=766,776 loops=1)

  • Sort Key: gtin_2.gtin_id
  • Sort Method: external sort Disk: 34512kB
41. 416.538 9,613.125 ↓ 6.9 766,776 1

WindowAgg (cost=938,288.75..940,498.09 rows=110,467 width=33) (actual time=9,049.554..9,613.125 rows=766,776 loops=1)

42. 503.576 9,196.587 ↓ 6.9 766,776 1

Sort (cost=938,288.75..938,564.92 rows=110,467 width=25) (actual time=9,049.546..9,196.587 rows=766,776 loops=1)

  • Sort Key: gtin_2.gtin_article_id, gtin_2.gtin_id
  • Sort Method: external merge Disk: 28568kB
43. 294.176 8,693.011 ↓ 6.9 766,776 1

Gather (cost=528,693.45..929,035.34 rows=110,467 width=25) (actual time=4,012.117..8,693.011 rows=766,776 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
44. 3,561.477 8,398.835 ↓ 5.6 255,592 3

Hash Join (cost=527,693.45..916,988.64 rows=46,028 width=25) (actual time=4,159.684..8,398.835 rows=255,592 loops=3)

  • Hash Cond: ((gtin_2.gtin_article_id = article_2.article_id) AND (gtin_2.gtin_dossier_id = article_2.article_dossier_id))
  • Join Filter: ((gtin_2.gtin_code)::text <> (article_2.article_gtin)::text)
  • Rows Removed by Join Filter: 3616173
45. 694.027 694.027 ↑ 1.2 3,871,765 3

Parallel Seq Scan on gtin gtin_2 (cost=0.00..234,156.23 rows=4,839,422 width=25) (actual time=0.015..694.027 rows=3,871,765 loops=3)

46. 2,207.067 4,143.331 ↑ 1.0 10,848,520 3

Hash (cost=301,391.38..301,391.38 rows=10,848,938 width=21) (actual time=4,143.331..4,143.331 rows=10,848,520 loops=3)

  • Buckets: 262144 Batches: 128 Memory Usage: 6645kB
47. 1,936.264 1,936.264 ↑ 1.0 10,848,520 3

Seq Scan on article article_2 (cost=0.00..301,391.38 rows=10,848,938 width=21) (actual time=0.032..1,936.264 rows=10,848,520 loops=3)