explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.123 34,588.205 ↓ 30.0 30 1

Limit (cost=2,703,775.46..2,703,775.46 rows=1 width=148) (actual time=34,588.081..34,588.205 rows=30 loops=1)

2. 0.084 34,588.082 ↓ 30.0 30 1

Sort (cost=2,703,775.46..2,703,775.46 rows=1 width=148) (actual time=34,588.080..34,588.082 rows=30 loops=1)

  • Sort Key: (ltrim((gtindoublon.gtin_code)::text, '0'::text)), article.article_id
  • Sort Method: quicksort Memory: 32kB
3. 219.694 34,587.998 ↓ 30.0 30 1

Nested Loop (cost=2,278,072.39..2,703,775.45 rows=1 width=148) (actual time=22,402.311..34,587.998 rows=30 loops=1)

  • Join Filter: ((article.article_id = gtin.gtin_article_id) OR (article.article_id = gtin2_1.gtin_article_id))
  • Rows Removed by Join Filter: 1959195
4. 63.833 156.034 ↑ 3,150.9 15 1

Hash Join (cost=189,298.44..374,015.23 rows=47,263 width=8) (actual time=84.013..156.034 rows=15 loops=1)

  • 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: 412
5. 28.381 33.499 ↑ 1.0 130,615 1

Bitmap Heap Scan on gtin gtin2_1 (cost=2,559.59..184,095.54 rows=136,665 width=25) (actual time=5.482..33.499 rows=130,615 loops=1)

  • Recheck Cond: (gtin_dossier_id = 15)
  • Heap Blocks: exact=3444
6. 5.118 5.118 ↑ 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.118..5.118 rows=130,620 loops=1)

  • Index Cond: (gtin_dossier_id = 15)
7. 27.361 58.702 ↑ 1.0 130,615 1

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

  • Buckets: 262144 Batches: 2 Memory Usage: 5861kB
8. 26.110 31.341 ↑ 1.0 130,615 1

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

  • Recheck Cond: (gtin_dossier_id = 15)
  • Heap Blocks: exact=3444
9. 5.231 5.231 ↑ 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.231..5.231 rows=130,620 loops=1)

  • Index Cond: (gtin_dossier_id = 15)
10. 373.691 34,212.270 ↓ 6,874.5 130,615 15

Materialize (cost=2,088,773.95..2,314,045.32 rows=19 width=129) (actual time=1,309.520..2,280.818 rows=130,615 loops=15)

11. 18.978 33,838.579 ↓ 6,874.5 130,615 1

Nested Loop (cost=2,088,773.95..2,314,045.23 rows=19 width=129) (actual time=19,642.733..33,838.579 rows=130,615 loops=1)

12. 139.134 33,299.773 ↓ 88.3 129,957 1

Nested Loop Left Join (cost=2,088,773.51..2,307,487.21 rows=1,472 width=120) (actual time=19,642.703..33,299.773 rows=129,957 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: 519824
13. 7,898.352 23,543.821 ↓ 88.3 129,957 1

Nested Loop Left Join (cost=1,139,022.01..1,355,647.97 rows=1,472 width=107) (actual time=10,020.017..23,543.821 rows=129,957 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: 84991224
14. 160.267 1,870.027 ↓ 88.3 129,957 1

Nested Loop Left Join (cost=189,270.51..403,808.72 rows=1,472 width=94) (actual time=73.718..1,870.027 rows=129,957 loops=1)

15. 40.154 1,579.803 ↓ 88.3 129,957 1

Nested Loop Left Join (cost=189,270.24..403,377.52 rows=1,472 width=88) (actual time=73.712..1,579.803 rows=129,957 loops=1)

  • Join Filter: (condachat.condachat_id = refachat.refachat_condachat_id)
  • Rows Removed by Join Filter: 884
16. 56.035 1,149.778 ↓ 88.3 129,957 1

Nested Loop Left Join (cost=189,269.82..402,605.04 rows=1,472 width=79) (actual time=73.703..1,149.778 rows=129,957 loops=1)

  • Join Filter: (condachat.condachat_dossier_id = produit.produit_dossier_id)
17. 74.991 703.872 ↓ 88.3 129,957 1

Nested Loop Left Join (cost=189,269.40..401,826.10 rows=1,472 width=79) (actual time=73.695..703.872 rows=129,957 loops=1)

18. 125.928 239.010 ↓ 88.3 129,957 1

Hash Join (cost=189,268.96..394,818.83 rows=1,472 width=48) (actual time=73.682..239.010 rows=129,957 loops=1)

  • Hash Cond: (produit.produit_id = article.article_produit_id)
19. 40.436 45.768 ↓ 1.0 129,957 1

Bitmap Heap Scan on produit (cost=2,366.10..205,329.18 rows=126,150 width=8) (actual time=6.316..45.768 rows=129,957 loops=1)

  • Recheck Cond: (produit_dossier_id = 15)
  • Heap Blocks: exact=7561
20. 5.332 5.332 ↓ 1.0 129,966 1

Bitmap Index Scan on produit_dossier_id_sk (cost=0.00..2,334.56 rows=126,150 width=0) (actual time=5.332..5.332 rows=129,966 loops=1)

  • Index Cond: (produit_dossier_id = 15)
21. 27.823 67.314 ↓ 1.0 129,957 1

Hash (cost=184,207.73..184,207.73 rows=126,571 width=44) (actual time=67.314..67.314 rows=129,957 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 6032kB
22. 34.538 39.491 ↓ 1.0 129,957 1

Bitmap Heap Scan on article (cost=2,373.36..184,207.73 rows=126,571 width=44) (actual time=5.466..39.491 rows=129,957 loops=1)

  • Recheck Cond: (article_dossier_id = 15)
  • Heap Blocks: exact=4505
23. 4.953 4.953 ↓ 1.0 129,968 1

Bitmap Index Scan on article_produit_sk (cost=0.00..2,341.72 rows=126,571 width=0) (actual time=4.953..4.953 rows=129,968 loops=1)

  • Index Cond: (article_dossier_id = 15)
24. 389.871 389.871 ↑ 1.0 1 129,957

Index Scan using articleden_ak on articleden (cost=0.43..4.76 rows=1 width=35) (actual time=0.003..0.003 rows=1 loops=129,957)

  • Index Cond: ((article.article_id = articleden_article_id) AND (articleden_dossier_id = 15))
25. 389.871 389.871 ↑ 1.0 1 129,957

Index Scan using condachat_ak on condachat (cost=0.42..0.52 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=129,957)

  • Index Cond: ((condachat_dossier_id = 15) AND (condachat_produit_id = article.article_produit_id))
  • Filter: condachat_principal
  • Rows Removed by Filter: 0
26. 389.871 389.871 ↑ 1.0 1 129,957

Index Scan using refachat_ak on refachat (cost=0.42..0.51 rows=1 width=25) (actual time=0.003..0.003 rows=1 loops=129,957)

  • Index Cond: (refachat_article_id = article.article_id)
  • Filter: ((refachat_dossier_id = 15) AND (refachat_dossier_id = article.article_dossier_id))
27. 129.957 129.957 ↑ 1.0 1 129,957

Index Scan using fournisseur_pk on fournisseur (cost=0.28..0.29 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=129,957)

  • Index Cond: (condachat.condachat_fournisseur_id = fournisseur_id)
28. 3,829.279 13,775.442 ↓ 109.0 654 129,957

Materialize (cost=949,751.50..951,684.70 rows=6 width=21) (actual time=0.075..0.106 rows=654 loops=129,957)

29. 61.691 9,946.163 ↓ 109.0 654 1

Subquery Scan on gtin1 (cost=949,751.50..951,684.67 rows=6 width=21) (actual time=9,779.743..9,946.163 rows=654 loops=1)

  • Filter: ((gtin1.index = 1) AND (gtin1.gtin_dossier_id = 15))
  • Rows Removed by Filter: 766122
30. 585.987 9,884.472 ↓ 6.9 766,776 1

Sort (cost=949,751.50..950,027.67 rows=110,467 width=33) (actual time=9,779.740..9,884.472 rows=766,776 loops=1)

  • Sort Key: gtin_1.gtin_id
  • Sort Method: external sort Disk: 34512kB
31. 395.140 9,298.485 ↓ 6.9 766,776 1

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

32. 491.982 8,903.345 ↓ 6.9 766,776 1

Sort (cost=938,288.75..938,564.92 rows=110,467 width=25) (actual time=8,760.373..8,903.345 rows=766,776 loops=1)

  • Sort Key: gtin_1.gtin_article_id, gtin_1.gtin_id
  • Sort Method: external merge Disk: 28568kB
33. 293.451 8,411.363 ↓ 6.9 766,776 1

Gather (cost=528,693.45..929,035.34 rows=110,467 width=25) (actual time=3,884.338..8,411.363 rows=766,776 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
34. 3,377.394 8,117.912 ↓ 5.6 255,592 3

Hash Join (cost=527,693.45..916,988.64 rows=46,028 width=25) (actual time=4,071.629..8,117.912 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. 685.419 685.419 ↑ 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..685.419 rows=3,871,765 loops=3)

36. 2,134.260 4,055.099 ↑ 1.0 10,848,520 3

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

  • Buckets: 262144 Batches: 128 Memory Usage: 6645kB
37. 1,920.839 1,920.839 ↑ 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.017..1,920.839 rows=10,848,520 loops=3)

38. 0.000 9,616.818 ↑ 1.5 4 129,957

Materialize (cost=949,751.50..951,684.70 rows=6 width=21) (actual time=0.074..0.074 rows=4 loops=129,957)

39. 55.874 9,622.677 ↑ 1.5 4 1

Subquery Scan on gtin2 (cost=949,751.50..951,684.67 rows=6 width=21) (actual time=9,622.584..9,622.677 rows=4 loops=1)

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

Sort (cost=949,751.50..950,027.67 rows=110,467 width=33) (actual time=9,462.085..9,566.803 rows=766,776 loops=1)

  • Sort Key: gtin_2.gtin_id
  • Sort Method: external sort Disk: 34512kB
41. 390.333 8,977.271 ↓ 6.9 766,776 1

WindowAgg (cost=938,288.75..940,498.09 rows=110,467 width=33) (actual time=8,443.920..8,977.271 rows=766,776 loops=1)

42. 482.828 8,586.938 ↓ 6.9 766,776 1

Sort (cost=938,288.75..938,564.92 rows=110,467 width=25) (actual time=8,443.912..8,586.938 rows=766,776 loops=1)

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

Gather (cost=528,693.45..929,035.34 rows=110,467 width=25) (actual time=3,752.240..8,104.110 rows=766,776 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
44. 3,245.186 7,809.165 ↓ 5.6 255,592 3

Hash Join (cost=527,693.45..916,988.64 rows=46,028 width=25) (actual time=3,905.547..7,809.165 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. 673.891 673.891 ↑ 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.014..673.891 rows=3,871,765 loops=3)

46. 2,044.588 3,890.088 ↑ 1.0 10,848,520 3

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

  • Buckets: 262144 Batches: 128 Memory Usage: 6645kB
47. 1,845.500 1,845.500 ↑ 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.022..1,845.500 rows=10,848,520 loops=3)

48. 519.828 519.828 ↑ 1.0 1 129,957

Index Scan using gtin_article_sk on gtin gtindoublon (cost=0.43..4.45 rows=1 width=21) (actual time=0.003..0.004 rows=1 loops=129,957)

  • Index Cond: ((gtin_dossier_id = 15) AND (gtin_article_id = article.article_id))