explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lUsn

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 87,958.287 ↑ 1.0 1 1

Unique (cost=847,346.51..847,346.53 rows=1 width=585) (actual time=87,958.264..87,958.287 rows=1 loops=1)

  • Output: p.produit_id, (COALESCE(cp_ean.valeur, cp_isbn.valeur, p.code)), p.asin, pl.nom_plateforme, 'achat'::text, (COALESCE(pt.ttl, $0, 172800)), apt.date_updated
  • JIT:
  • Functions: 383
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 30.538 ms, Inlining 113.641 ms, Optimization 1614.723 ms, Emission 983.794 ms, Total 2742.696 ms
  • Execution Time: 88105.786 msEXPLAIN
2.          

Initplan (for Unique)

3. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.param (cost=0.00..1.51 rows=1 width=4) (never executed)

  • Output: param.valeur_int
  • Filter: ((param.name)::text = 'ttl_defaut_achat'::text)
4. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on public.adminpricing_typecode (cost=0.00..1.04 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=1)

  • Output: adminpricing_typecode.id
  • Filter: ((adminpricing_typecode.libelle)::text = 'EAN'::text)
  • Rows Removed by Filter: 2
5. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on public.adminpricing_typecode adminpricing_typecode_1 (cost=0.00..1.04 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1)

  • Output: adminpricing_typecode_1.id
  • Filter: ((adminpricing_typecode_1.libelle)::text = 'ISBN'::text)
  • Rows Removed by Filter: 2
6. 0.076 87,958.264 ↓ 8.0 8 1

Sort (cost=847,342.92..847,342.93 rows=1 width=585) (actual time=87,958.263..87,958.264 rows=8 loops=1)

  • Output: p.produit_id, (COALESCE(cp_ean.valeur, cp_isbn.valeur, p.code)), p.asin, pl.nom_plateforme, 'achat'::text, (COALESCE(pt.ttl, $0, 172800)), apt.date_updated
  • Sort Key: p.produit_id, (COALESCE(cp_ean.valeur, cp_isbn.valeur, p.code)), p.asin, pl.nom_plateforme, (COALESCE(pt.ttl, $0, 172800)), apt.date_updated
  • Sort Method: quicksort Memory: 26kB
7. 0.031 87,958.188 ↓ 8.0 8 1

Nested Loop Left Join (cost=37,298.60..847,342.91 rows=1 width=585) (actual time=8,005.531..87,958.188 rows=8 loops=1)

  • Output: p.produit_id, COALESCE(cp_ean.valeur, cp_isbn.valeur, p.code), p.asin, pl.nom_plateforme, 'achat'::text, COALESCE(pt.ttl, $0, 172800), apt.date_updated
  • Inner Unique: true
  • Join Filter: (nep.marketplace_id = pl.plateforme_id)
  • Filter: (nep.nonexistent_product_id IS NULL)
8. 0.025 87,958.069 ↓ 8.0 8 1

Nested Loop Left Join (cost=37,298.04..847,341.38 rows=1 width=74) (actual time=8,005.499..87,958.069 rows=8 loops=1)

  • Output: p.produit_id, p.code, p.asin, pl.nom_plateforme, pl.plateforme_id, apt.date_updated, pt.ttl, cp_ean.valeur, cp_isbn.valeur
  • Inner Unique: true
9. 0.025 87,958.012 ↓ 8.0 8 1

Nested Loop Left Join (cost=37,297.47..847,340.60 rows=1 width=63) (actual time=8,005.482..87,958.012 rows=8 loops=1)

  • Output: p.produit_id, p.code, p.asin, pl.nom_plateforme, pl.plateforme_id, apt.date_updated, pt.ttl, cp_ean.valeur
  • Inner Unique: true
10. 0.036 87,957.859 ↓ 8.0 8 1

Nested Loop Left Join (cost=37,296.90..847,339.82 rows=1 width=52) (actual time=8,005.439..87,957.859 rows=8 loops=1)

  • Output: p.produit_id, p.code, p.asin, pl.nom_plateforme, pl.plateforme_id, apt.date_updated, pt.ttl
  • Inner Unique: true
  • Join Filter: (pt.marketplace_id = pl.plateforme_id)
11. 0.035 87,957.703 ↓ 8.0 8 1

Nested Loop (cost=37,296.33..847,338.09 rows=1 width=48) (actual time=8,005.399..87,957.703 rows=8 loops=1)

  • Output: p.produit_id, p.code, p.asin, pl.nom_plateforme, pl.plateforme_id, apt.date_updated
  • Join Filter: ((s.societe_id = psa.societe_id_fk) AND (pl_vente.plateforme_id = psa.plateforme_id_fk))
  • Rows Removed by Join Filter: 50
12. 0.000 87,957.532 ↓ 8.0 8 1

Nested Loop Anti Join (cost=37,296.18..847,337.63 rows=1 width=64) (actual time=8,005.377..87,957.532 rows=8 loops=1)

  • Output: p.produit_id, p.code, p.asin, iapfps.societe_id_fk, s.societe_id, i.plateforme_vente_id_fk, pl.nom_plateforme, pl.plateforme_id, pl_vente.plateforme_id, apt.date_updated
  • Join Filter: (apt.plateforme_id_fk = pl.plateforme_id)
  • Rows Removed by Join Filter: 9564376
13. 8,607.297 25,264.013 ↓ 7,093,864.0 7,093,864 1

Nested Loop (cost=37,295.61..847,333.71 rows=1 width=56) (actual time=1,324.074..25,264.013 rows=7,093,864 loops=1)

  • Output: p.produit_id, p.code, p.asin, iapfps.societe_id_fk, s.societe_id, i.plateforme_vente_id_fk, pl.nom_plateforme, pl.plateforme_id, pl_vente.plateforme_id
  • Join Filter: (i.plateforme_vente_id_fk = pl_vente.plateforme_id)
  • Rows Removed by Join Filter: 163158872
14. 0.068 0.068 ↑ 1.0 24 1

Index Only Scan using plateforme_plateforme_id_actif_idx on public.plateforme pl_vente (cost=0.14..2.70 rows=24 width=4) (actual time=0.007..0.068 rows=24 loops=1)

  • Output: pl_vente.plateforme_id, pl_vente.actif
  • Heap Fetches: 0
15. 14,276.546 16,656.648 ↓ 7,093,864.0 7,093,864 24

Materialize (cost=37,295.47..847,330.66 rows=1 width=52) (actual time=55.173..694.027 rows=7,093,864 loops=24)

  • Output: p.produit_id, p.code, p.asin, iapfps.societe_id_fk, s.societe_id, i.plateforme_vente_id_fk, pl.nom_plateforme, pl.plateforme_id
16. 0.000 2,380.102 ↓ 7,093,864.0 7,093,864 1

Gather (cost=37,295.47..847,330.65 rows=1 width=52) (actual time=1,324.055..2,380.102 rows=7,093,864 loops=1)

  • Output: p.produit_id, p.code, p.asin, iapfps.societe_id_fk, s.societe_id, i.plateforme_vente_id_fk, pl.nom_plateforme, pl.plateforme_id
  • Workers Planned: 2
  • Workers Launched: 2
  • JIT for worker 0:
  • Functions: 106
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 8.382 ms, Inlining 37.214 ms, Optimization 416.315 ms, Emission 254.074 ms, Total 715.985 ms
  • JIT for worker 1:
  • Functions: 106
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 8.060 ms, Inlining 35.747 ms, Optimization 404.484 ms, Emission 241.734 ms, Total 690.025 ms
17. 345.671 2,643.911 ↓ 2,364,621.0 2,364,621 3 / 3

Hash Left Join (cost=36,295.47..846,330.55 rows=1 width=52) (actual time=1,024.886..2,643.911 rows=2,364,621 loops=3)

  • Output: p.produit_id, p.code, p.asin, iapfps.societe_id_fk, s.societe_id, i.plateforme_vente_id_fk, pl.nom_plateforme, pl.plateforme_id
  • Inner Unique: true
  • Hash Cond: (p.produit_id = ps.produit_id)
  • Filter: (ps.id IS NULL)
  • Worker 0: actual time=836.941..836.941 rows=0 loops=1
  • Worker 1: actual time=914.502..5771.576 rows=7093864 loops=1
18. 243.197 2,297.075 ↑ 1.3 2,364,621 3 / 3

Merge Join (cost=35,853.92..838,101.69 rows=2,966,595 width=52) (actual time=1,023.707..2,297.075 rows=2,364,621 loops=3)

  • Output: p.produit_id, p.code, p.asin, iapfps.societe_id_fk, s.societe_id, i.plateforme_vente_id_fk, pl.nom_plateforme, pl.plateforme_id
  • Inner Unique: true
  • Merge Cond: (c.template_pricing_categorie_id_fk = tpc.template_pricing_categorie_id)
  • Worker 0: actual time=836.940..836.940 rows=0 loops=1
  • Worker 1: actual time=910.968..4731.070 rows=7093864 loops=1
19. 271.317 2,053.842 ↑ 1.2 2,364,621 3 / 3

Merge Join (cost=35,848.28..1,149,758.54 rows=2,925,684 width=60) (actual time=1,023.669..2,053.842 rows=2,364,621 loops=3)

  • Output: p.produit_id, p.code, p.asin, c.template_pricing_categorie_id_fk, iapfps.template_pricing_categorie_id_fk, iapfps.societe_id_fk, s.societe_id, i.plateforme_vente_id_fk, pl.nom_plateforme, pl.plateforme_id
  • Merge Cond: (c.template_pricing_categorie_id_fk = iapfps.template_pricing_categorie_id_fk)
  • Worker 0: actual time=836.940..836.940 rows=0 loops=1
  • Worker 1: actual time=910.853..4001.372 rows=7093864 loops=1
20. 33.080 1,685.134 ↑ 2.4 295,578 3 / 3

Nested Loop (cost=35,567.78..1,561,736.98 rows=694,730 width=30) (actual time=1,022.304..1,685.134 rows=295,578 loops=3)

  • Output: p.produit_id, p.code, p.asin, c.template_pricing_categorie_id_fk
  • Worker 0: actual time=836.939..836.939 rows=0 loops=1
  • Worker 1: actual time=906.762..2895.250 rows=886733 loops=1
21. 951.109 951.109 ↓ 0.0 0 3 / 3

Parallel Index Scan using categorie_template_id_fk_active_ix_2 on public.categorie c (cost=0.42..16,011.08 rows=1 width=8) (actual time=951.101..951.109 rows=0 loops=3)

  • Output: c.categorie_id, c.template_pricing_categorie_id_fk
  • Index Cond: (c.active = 1)
  • Filter: ((c.nb_produit > 0) AND (c.categorie_id = 36381) AND (c.is_selling = 1))
  • Rows Removed by Filter: 43836
  • Worker 0: actual time=836.938..836.938 rows=0 loops=1
  • Worker 1: actual time=693.155..693.177 rows=1 loops=1
22. 640.862 700.945 ↑ 1.9 886,733 1 / 3

Bitmap Heap Scan on public.produit p (cost=35,567.36..1,529,052.39 rows=1,667,351 width=30) (actual time=213.589..2,102.836 rows=886,733 loops=1)

  • Output: p.produit_id, p.code, p.asin, p.categorie_id_fk, p.date_created, p.date_updated, p.deleted, p.poids
  • Recheck Cond: ((p.categorie_id_fk = 36381) AND (p.deleted = 0))
  • Worker 1: actual time=213.589..2102.836 rows=886733 loops=1
23. 60.083 60.083 ↑ 1.9 886,733 1 / 3

Bitmap Index Scan on produit_categorie_deleted_ix (cost=0.00..35,150.52 rows=1,667,351 width=0) (actual time=180.250..180.250 rows=886,733 loops=1)

  • Index Cond: ((p.categorie_id_fk = 36381) AND (p.deleted = 0))
  • Worker 1: actual time=180.250..180.250 rows=886733 loops=1
24. 96.326 97.391 ↓ 3,726.0 7,094,397 1 / 3

Sort (cost=273.90..278.66 rows=1,904 width=30) (actual time=4.027..292.173 rows=7,094,397 loops=1)

  • Output: iapfps.template_pricing_categorie_id_fk, iapfps.societe_id_fk, s.societe_id, i.plateforme_vente_id_fk, pl.nom_plateforme, pl.plateforme_id
  • Sort Key: iapfps.template_pricing_categorie_id_fk
  • Worker 1: Sort Method: quicksort Memory: 414kB
  • Worker 1: actual time=4.027..292.173 rows=7094397 loops=1
25. 0.189 1.065 ↓ 2.1 4,066 1 / 3

Hash Join (cost=14.79..170.19 rows=1,904 width=30) (actual time=0.411..3.196 rows=4,066 loops=1)

  • Output: iapfps.template_pricing_categorie_id_fk, iapfps.societe_id_fk, s.societe_id, i.plateforme_vente_id_fk, pl.nom_plateforme, pl.plateforme_id
  • Inner Unique: true
  • Hash Cond: (iapfps.algorithme_pricing_id_fk = ap.algorithme_pricing_id)
  • Worker 1: actual time=0.411..3.196 rows=4066 loops=1
26. 0.161 0.834 ↓ 2.1 4,066 1 / 3

Hash Join (cost=8.15..158.42 rows=1,904 width=34) (actual time=0.148..2.502 rows=4,066 loops=1)

  • Output: iapfps.template_pricing_categorie_id_fk, iapfps.societe_id_fk, iapfps.algorithme_pricing_id_fk, s.societe_id, i.plateforme_vente_id_fk, pl.nom_plateforme, pl.plateforme_id
  • Inner Unique: true
  • Hash Cond: (iapfps.societe_id_fk = s.societe_id)
  • Worker 1: actual time=0.148..2.502 rows=4066 loops=1
27. 0.149 0.666 ↓ 2.0 4,066 1 / 3

Hash Join (cost=6.40..150.59 rows=2,036 width=30) (actual time=0.119..1.999 rows=4,066 loops=1)

  • Output: iapfps.template_pricing_categorie_id_fk, iapfps.societe_id_fk, iapfps.algorithme_pricing_id_fk, i.plateforme_vente_id_fk, pl.nom_plateforme, pl.plateforme_id
  • Inner Unique: true
  • Hash Cond: (i.plateforme_achat_id_fk = pl.plateforme_id)
  • Worker 1: actual time=0.119..1.999 rows=4066 loops=1
28. 0.156 0.510 ↑ 1.0 4,066 1 / 3

Hash Join (cost=4.95..136.56 rows=4,071 width=20) (actual time=0.087..1.529 rows=4,066 loops=1)

  • Output: iapfps.template_pricing_categorie_id_fk, iapfps.societe_id_fk, iapfps.algorithme_pricing_id_fk, i.plateforme_achat_id_fk, i.plateforme_vente_id_fk
  • Inner Unique: true
  • Hash Cond: (iapfps.itineraire_id_fk = i.itineraire_id)
  • Worker 1: actual time=0.087..1.529 rows=4066 loops=1
29. 0.336 0.336 ↑ 1.0 4,066 1 / 3

Seq Scan on public.itineraire_algorithme_pricing_fdp_poids_set iapfps (cost=0.00..120.64 rows=4,071 width=16) (actual time=0.023..1.007 rows=4,066 loops=1)

  • Output: iapfps.template_pricing_categorie_id_fk, iapfps.societe_id_fk, iapfps.itineraire_id_fk, iapfps.algorithme_pricing_id_fk
  • Filter: (iapfps.actif = 1)
  • Rows Removed by Filter: 865
  • Worker 1: actual time=0.023..1.007 rows=4066 loops=1
30. 0.006 0.018 ↑ 1.0 158 1 / 3

Hash (cost=2.98..2.98 rows=158 width=12) (actual time=0.053..0.053 rows=158 loops=1)

  • Output: i.itineraire_id, i.plateforme_achat_id_fk, i.plateforme_vente_id_fk
  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
  • Worker 1: actual time=0.053..0.053 rows=158 loops=1
31. 0.012 0.012 ↑ 1.0 158 1 / 3

Seq Scan on public.itineraire i (cost=0.00..2.98 rows=158 width=12) (actual time=0.022..0.035 rows=158 loops=1)

  • Output: i.itineraire_id, i.plateforme_achat_id_fk, i.plateforme_vente_id_fk
  • Filter: (i.actif = 1)
  • Worker 1: actual time=0.022..0.035 rows=158 loops=1
32. 0.002 0.008 ↑ 1.1 11 1 / 3

Hash (cost=1.30..1.30 rows=12 width=14) (actual time=0.023..0.023 rows=11 loops=1)

  • Output: pl.nom_plateforme, pl.plateforme_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Worker 1: actual time=0.023..0.023 rows=11 loops=1
33. 0.006 0.006 ↑ 1.1 11 1 / 3

Seq Scan on public.plateforme pl (cost=0.00..1.30 rows=12 width=14) (actual time=0.015..0.018 rows=11 loops=1)

  • Output: pl.nom_plateforme, pl.plateforme_id
  • Filter: (pl.calcul_prix_achat = 1)
  • Rows Removed by Filter: 13
  • Worker 1: actual time=0.015..0.018 rows=11 loops=1
34. 0.002 0.007 ↓ 1.1 31 1 / 3

Hash (cost=1.39..1.39 rows=29 width=4) (actual time=0.020..0.020 rows=31 loops=1)

  • Output: s.societe_id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Worker 1: actual time=0.020..0.020 rows=31 loops=1
35. 0.005 0.005 ↓ 1.1 31 1 / 3

Seq Scan on public.societe s (cost=0.00..1.39 rows=29 width=4) (actual time=0.010..0.015 rows=31 loops=1)

  • Output: s.societe_id
  • Filter: (s.active = 1)
  • Worker 1: actual time=0.010..0.015 rows=31 loops=1
36. 0.007 0.043 ↓ 1.0 162 1 / 3

Hash (cost=4.69..4.69 rows=156 width=4) (actual time=0.128..0.128 rows=162 loops=1)

  • Output: ap.algorithme_pricing_id
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
  • Worker 1: actual time=0.128..0.128 rows=162 loops=1
37. 0.036 0.036 ↓ 1.0 162 1 / 3

Index Only Scan using algorithme_pricing_id_pkey on public.algorithme_pricing ap (cost=0.14..4.69 rows=156 width=4) (actual time=0.051..0.107 rows=162 loops=1)

  • Output: ap.algorithme_pricing_id
  • Heap Fetches: 45
  • Worker 1: actual time=0.051..0.107 rows=162 loops=1
38. 0.036 0.036 ↑ 5.6 55 1 / 3

Index Scan using template_pricing_categorie_id_pkey on public.template_pricing_categorie tpc (cost=0.27..26.37 rows=307 width=4) (actual time=0.038..0.108 rows=55 loops=1)

  • Output: tpc.template_pricing_categorie_id, tpc.nom, tpc.active, tpc.date_created, tpc.tva_achat, tpc.type_code_id, tpc.commentaires, tpc.modele_decision_id_fk, tpc.customer_service_fee, tpc.it_service_fee, tpc.logistics_fee, tpc.packing_fee, tpc.sourcing_fee, tpc.indirect_costs, tpc.ships_domestically_only, tpc.options
  • Filter: (tpc.active = 1)
  • Rows Removed by Filter: 20
  • Worker 1: actual time=0.038..0.108 rows=55 loops=1
39. 0.405 1.165 ↓ 1.0 13,473 1 / 3

Hash (cost=275.69..275.69 rows=13,269 width=8) (actual time=3.495..3.496 rows=13,473 loops=1)

  • Output: ps.produit_id, ps.id
  • Buckets: 16384 Batches: 1 Memory Usage: 655kB
  • Worker 1: actual time=3.495..3.496 rows=13473 loops=1
40. 0.761 0.761 ↓ 1.0 13,473 1 / 3

Seq Scan on public.adminpricing_produitstock ps (cost=0.00..275.69 rows=13,269 width=8) (actual time=0.024..2.282 rows=13,473 loops=1)

  • Output: ps.produit_id, ps.id
  • Worker 1: actual time=0.024..2.282 rows=13473 loops=1
41. 63,844.776 63,844.776 ↑ 8.0 2 7,093,864

Index Scan using adminpricing_pricingtracking_produit_id_fk_285101c9 on public.adminpricing_pricingtracking apt (cost=0.57..3.72 rows=16 width=16) (actual time=0.006..0.009 rows=2 loops=7,093,864)

  • Output: apt.id, apt.date_updated, apt.plateforme_id_fk, apt.produit_id_fk
  • Index Cond: (apt.produit_id_fk = p.produit_id)
42. 0.136 0.136 ↓ 1.8 7 8

Index Scan using plateforme_societe_account_ix_2 on public.plateforme_societe_account psa (cost=0.15..0.40 rows=4 width=8) (actual time=0.007..0.017 rows=7 loops=8)

  • Output: psa.plateforme_societe_account_id, psa.credentials, psa.plateforme_id_fk, psa.societe_id_fk, psa.actif, psa.principal, psa.date_last_block, psa.date_last_used, psa.is_pricing, psa.accept_orders, psa.min_sent_feeds_count, psa.is_in_iota, psa.fixed_commission, psa.variable_commission, psa.tn_with_accept_orders, psa.last_refeed_update
  • Index Cond: (psa.societe_id_fk = iapfps.societe_id_fk)
  • Filter: ((psa.actif = 1) AND (psa.principal = 1))
  • Rows Removed by Filter: 3
43. 0.120 0.120 ↑ 20.0 1 8

Index Scan using produit_ttl_produit_id_fk_5c472ea5 on public.produit_ttl pt (cost=0.56..1.48 rows=20 width=12) (actual time=0.015..0.015 rows=1 loops=8)

  • Output: pt.produit_ttl_id, pt.produit_id_fk, pt.ttl, pt.marketplace_id
  • Index Cond: (p.produit_id = pt.produit_id_fk)
44. 0.128 0.128 ↑ 11.0 1 8

Index Scan using adminpricing_codeproduit_8eb6132c on public.adminpricing_codeproduit cp_ean (cost=0.57..2.85 rows=11 width=15) (actual time=0.016..0.016 rows=1 loops=8)

  • Output: cp_ean.id, cp_ean.valeur, cp_ean.produit_id, cp_ean.type_code_id
  • Index Cond: (cp_ean.produit_id = p.produit_id)
  • Filter: (cp_ean.type_code_id = $1)
  • Rows Removed by Filter: 2
45. 0.032 0.032 ↑ 11.0 1 8

Index Scan using adminpricing_codeproduit_8eb6132c on public.adminpricing_codeproduit cp_isbn (cost=0.57..2.85 rows=11 width=15) (actual time=0.004..0.004 rows=1 loops=8)

  • Output: cp_isbn.id, cp_isbn.valeur, cp_isbn.produit_id, cp_isbn.type_code_id
  • Index Cond: (cp_isbn.produit_id = p.produit_id)
  • Filter: (cp_isbn.type_code_id = $2)
  • Rows Removed by Filter: 1
46. 0.088 0.088 ↓ 0.0 0 8

Index Scan using adminpricing_nonexistent_produit_id_marketplace_i_a17d1865_uniq on public.adminpricing_nonexistentproducts nep (cost=0.56..1.51 rows=2 width=16) (actual time=0.011..0.011 rows=0 loops=8)

  • Output: nep.nonexistent_product_id, nep."exists", nep.date_created, nep.marketplace_id, nep.produit_id
  • Index Cond: (nep.produit_id = p.produit_id)
  • Filter: (NOT nep."exists")
Planning time : 33.851 ms