explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ck1g : Optimization for: plan #WJCO

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 127,440.866 ↓ 7.0 7 1

Unique (cost=847,346.57..847,346.59 rows=1 width=585) (actual time=127,440.837..127,440.866 rows=7 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: 385
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 29.936 ms, Inlining 115.446 ms, Optimization 1640.222 ms, Emission 975.923 ms, Total 2761.527 ms
  • Execution Time: 127592.496 msEXPLAIN
2.          

Initplan (for Unique)

3. 0.044 0.044 ↑ 1.0 1 1

Seq Scan on public.param (cost=0.00..1.51 rows=1 width=4) (actual time=0.042..0.044 rows=1 loops=1)

  • Output: param.valeur_int
  • Filter: ((param.name)::text = 'ttl_defaut_achat'::text)
  • Rows Removed by Filter: 40
4. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on public.adminpricing_typecode (cost=0.00..1.04 rows=1 width=4) (actual time=0.011..0.011 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.212 127,440.838 ↓ 56.0 56 1

Sort (cost=847,342.99..847,342.99 rows=1 width=585) (actual time=127,440.836..127,440.838 rows=56 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: 32kB
7. 0.263 127,440.626 ↓ 56.0 56 1

Nested Loop Left Join (cost=37,298.60..847,342.98 rows=1 width=585) (actual time=15,639.893..127,440.626 rows=56 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.183 127,439.803 ↓ 56.0 56 1

Nested Loop Left Join (cost=37,298.04..847,341.44 rows=1 width=74) (actual time=15,639.859..127,439.803 rows=56 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.196 127,439.340 ↓ 56.0 56 1

Nested Loop Left Join (cost=37,297.47..847,340.66 rows=1 width=63) (actual time=15,639.845..127,439.340 rows=56 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.176 127,437.352 ↓ 56.0 56 1

Nested Loop Left Join (cost=37,296.90..847,339.88 rows=1 width=52) (actual time=15,639.797..127,437.352 rows=56 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.285 127,435.216 ↓ 56.0 56 1

Nested Loop (cost=37,296.33..847,338.15 rows=1 width=48) (actual time=15,639.467..127,435.216 rows=56 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: 350
12. 2,645.935 127,434.035 ↓ 56.0 56 1

Nested Loop Anti Join (cost=37,296.18..847,337.69 rows=1 width=64) (actual time=15,639.447..127,434.035 rows=56 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: 7516256
13. 8,412.570 25,474.004 ↓ 7,093,864.0 7,093,864 1

Nested Loop (cost=37,295.61..847,333.71 rows=1 width=56) (actual time=1,533.979..25,474.004 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.074 0.074 ↑ 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.010..0.074 rows=24 loops=1)

  • Output: pl_vente.plateforme_id, pl_vente.actif
  • Heap Fetches: 0
15. 14,220.165 17,061.360 ↓ 7,093,864.0 7,093,864 24

Materialize (cost=37,295.47..847,330.66 rows=1 width=52) (actual time=63.917..710.890 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,841.195 ↓ 7,093,864.0 7,093,864 1

Gather (cost=37,295.47..847,330.65 rows=1 width=52) (actual time=1,533.918..2,841.195 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.124 ms, Inlining 36.875 ms, Optimization 439.210 ms, Emission 256.586 ms, Total 740.795 ms
  • JIT for worker 1:
  • Functions: 106
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 8.027 ms, Inlining 35.342 ms, Optimization 403.503 ms, Emission 241.583 ms, Total 688.456 ms
17. 360.243 2,991.833 ↓ 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,221.373..2,991.833 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=860.824..860.824 rows=0 loops=1
  • Worker 1: actual time=1482.831..6794.211 rows=7093864 loops=1
18. 245.680 2,630.408 ↑ 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,220.177..2,630.408 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=860.823..860.823 rows=0 loops=1
  • Worker 1: actual time=1479.246..5709.937 rows=7093864 loops=1
19. 283.799 2,384.690 ↑ 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,220.137..2,384.690 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=860.822..860.822 rows=0 loops=1
  • Worker 1: actual time=1479.125..4972.786 rows=7093864 loops=1
20. 35.207 1,999.477 ↑ 2.4 295,578 3 / 3

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

  • Output: p.produit_id, p.code, p.asin, c.template_pricing_categorie_id_fk
  • Worker 0: actual time=860.821..860.822 rows=0 loops=1
  • Worker 1: actual time=1475.014..3817.148 rows=886733 loops=1
21. 957.723 957.723 ↓ 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=957.715..957.723 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=860.820..860.820 rows=0 loops=1
  • Worker 1: actual time=691.863..691.887 rows=1 loops=1
22. 756.598 1,006.547 ↑ 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=783.122..3,019.642 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=783.122..3019.642 rows=886733 loops=1
23. 249.950 249.950 ↑ 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=749.849..749.849 rows=886,733 loops=1)

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

Sort (cost=273.90..278.66 rows=1,904 width=30) (actual time=4.045..304.242 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.045..304.242 rows=7094397 loops=1
25. 0.188 1.074 ↓ 2.1 4,066 1 / 3

Hash Join (cost=14.79..170.19 rows=1,904 width=30) (actual time=0.434..3.223 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.434..3.223 rows=4066 loops=1
26. 0.155 0.837 ↓ 2.1 4,066 1 / 3

Hash Join (cost=8.15..158.42 rows=1,904 width=34) (actual time=0.144..2.512 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.144..2.512 rows=4066 loops=1
27. 0.147 0.676 ↓ 2.0 4,066 1 / 3

Hash Join (cost=6.40..150.59 rows=2,036 width=30) (actual time=0.116..2.027 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.116..2.027 rows=4066 loops=1
28. 0.150 0.522 ↑ 1.0 4,066 1 / 3

Hash Join (cost=4.95..136.56 rows=4,071 width=20) (actual time=0.086..1.565 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.086..1.565 rows=4066 loops=1
29. 0.355 0.355 ↑ 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.024..1.065 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.024..1.065 rows=4066 loops=1
30. 0.006 0.017 ↑ 1.0 158 1 / 3

Hash (cost=2.98..2.98 rows=158 width=12) (actual time=0.051..0.051 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.051..0.051 rows=158 loops=1
31. 0.011 0.011 ↑ 1.0 158 1 / 3

Seq Scan on public.itineraire i (cost=0.00..2.98 rows=158 width=12) (actual time=0.020..0.033 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.020..0.033 rows=158 loops=1
32. 0.001 0.007 ↑ 1.1 11 1 / 3

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

  • Output: pl.nom_plateforme, pl.plateforme_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Worker 1: actual time=0.020..0.021 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.013..0.017 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.013..0.017 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.049 ↓ 1.0 162 1 / 3

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

  • Output: ap.algorithme_pricing_id
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
  • Worker 1: actual time=0.148..0.148 rows=162 loops=1
37. 0.043 0.043 ↓ 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.073..0.128 rows=162 loops=1)

  • Output: ap.algorithme_pricing_id
  • Heap Fetches: 45
  • Worker 1: actual time=0.073..0.128 rows=162 loops=1
38. 0.038 0.038 ↑ 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.040..0.113 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.040..0.113 rows=55 loops=1
39. 0.407 1.182 ↓ 1.0 13,473 1 / 3

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

  • Output: ps.produit_id, ps.id
  • Buckets: 16384 Batches: 1 Memory Usage: 655kB
  • Worker 1: actual time=3.546..3.546 rows=13473 loops=1
40. 0.775 0.775 ↓ 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.026..2.325 rows=13,473 loops=1)

  • Output: ps.produit_id, ps.id
  • Worker 1: actual time=0.026..2.325 rows=13473 loops=1
41. 99,314.096 99,314.096 ↑ 2.5 2 7,093,864

Index Scan using adminpricing_pricingtracking_produit_id_fk_285101c9 on public.adminpricing_pricingtracking apt (cost=0.57..3.92 rows=5 width=16) (actual time=0.009..0.014 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)
  • Filter: (((timezone('EDT'::text, now()))::timestamp with time zone - apt.date_updated) <= '7 days'::interval)
  • Rows Removed by Filter: 0
42. 0.896 0.896 ↓ 1.8 7 56

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.006..0.016 rows=7 loops=56)

  • 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. 1.960 1.960 ↑ 20.0 1 56

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.035..0.035 rows=1 loops=56)

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

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

  • 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.280 0.280 ↑ 11.0 1 56

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

  • 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.560 0.560 ↓ 0.0 0 56

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.010..0.010 rows=0 loops=56)

  • 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 : 32.015 ms