explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dGEn

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=2,945.52..5,520,887.05 rows=4 width=48) (actual rows= loops=)

  • Output: a0.daterappel, a0.daterappel, (COALESCE(sum(1), 0::bigint)), (COALESCE(sum(CASE WHEN (SubPlan 11) THEN 1 ELSE 0 END), 0::bigint)), (COALESCE(sum(CASE WHEN (a3.dhcontroleinv IS NULL) THEN 1 ELSE 0 END), 0::bigint)), (COALESCE(sum(CASE WHEN ((a3.ty (...)
2. 0.000 0.000 ↓ 0.0

WindowAgg (cost=2,945.52..5,520,887.05 rows=4 width=48) (actual rows= loops=)

  • Output: a0.daterappel, a0.daterappel, (COALESCE(sum(1), 0::bigint)), (COALESCE(sum(CASE WHEN (SubPlan 11) THEN 1 ELSE 0 END), 0::bigint)), (COALESCE(sum(CASE WHEN (a3.dhcontroleinv IS NULL) THEN 1 ELSE 0 END), 0::bigint)), (COALESCE(sum(CASE WHEN ( (...)
3. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,945.52..11,783.25 rows=4 width=48) (actual rows= loops=)

  • Output: a0.daterappel, (COALESCE(sum(1), 0::bigint)), (COALESCE(sum(CASE WHEN (SubPlan 11) THEN 1 ELSE 0 END), 0::bigint)), (COALESCE(sum(CASE WHEN (a3.dhcontroleinv IS NULL) THEN 1 ELSE 0 END), 0::bigint)), (COALESCE(sum(CASE WHEN ((a3.typeb (...)
4. 0.000 0.000 ↓ 0.0

Seq Scan on local140.gp_jourinv a0 (cost=0.00..1.06 rows=4 width=8) (actual rows= loops=)

  • Output: a0.id, a0.daterappel, a0.dhclotureinv, a0.utilclotureinv, a0.nomfichierclotureinv, a0.depose, a0.etatinv
  • Filter: (a0.etatinv <> 3)
5. 0.000 0.000 ↓ 0.0

Aggregate (cost=2,945.52..2,945.53 rows=1 width=18) (actual rows= loops=)

  • Output: COALESCE(sum(1), 0::bigint), COALESCE(sum(CASE WHEN (SubPlan 11) THEN 1 ELSE 0 END), 0::bigint), COALESCE(sum(CASE WHEN (a3.dhcontroleinv IS NULL) THEN 1 ELSE 0 END), 0::bigint), COALESCE(sum(CASE WHEN ((a3.typebi = 0) OR ((a3.t (...)
6. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on local140.gp_bi a3 (cost=11.40..37.44 rows=403 width=18) (actual rows= loops=)

  • Output: a3.id, a3.idclient, a3.mutation, a3.typebi, a3.daterappel, a3.numcab, a3.datecreation, a3.utilcreation, a3.msgrappel, a3.datecloture, a3.dateimprcloture, a3.utilimprcloture, a3.editatelier, a3.idrh, a3.modecontroleinv, a3. (...)
  • Recheck Cond: (a0.daterappel = a3.daterappel)
7. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on gp_bi_daterappel_idx (cost=0.00..11.30 rows=403 width=0) (actual rows= loops=)

  • Index Cond: (a0.daterappel = a3.daterappel)
8.          

SubPlan (forAggregate)

9. 0.000 0.000 ↓ 0.0

Limit (cost=0.29..7.19 rows=1 width=0) (actual rows= loops=)

  • Output: (1)
10. 0.000 0.000 ↓ 0.0

Index Scan using gp_invendu_origine_idx on local140.gp_invendu gp_invendu_2 (cost=0.29..165.80 rows=24 width=0) (actual rows= loops=)

  • Output: 1
  • Index Cond: (gp_invendu_2.idbiorigine = a3.id)
  • Filter: (gp_invendu_2.fichierdeclaratif IS NOT NULL)
11.          

SubPlan (forWindowAgg)

12. 0.000 0.000 ↓ 0.0

Aggregate (cost=688,617.50..688,617.52 rows=1 width=28) (actual rows= loops=)

  • Output: round(((sum(CASE WHEN CASE WHEN (gp_bi.daterappel = COALESCE(gp_bi.datecloture, gp_bi.daterappel)) THEN (gp_bi.dhcontroleinv IS NOT NULL) ELSE false END THEN ((COALESCE((gp_invendu.qtefournip2000)::bigint, COALESCE((SubPlan 1), 0::b (...)
13. 0.000 0.000 ↓ 0.0

Result (cost=45.50..2,584.41 rows=16,361 width=28) (actual rows= loops=)

  • Output: gp_bi.daterappel, gp_bi.datecloture, gp_bi.dhcontroleinv, gp_invendu.qtefournip2000, gp_invendu.idparution, gp_invendu.idclient
  • One-Time Filter: (a0.etatinv = 1)
14. 0.000 0.000 ↓ 0.0

Hash Join (cost=45.50..2,584.41 rows=16,361 width=28) (actual rows= loops=)

  • Output: gp_bi.daterappel, gp_bi.datecloture, gp_bi.dhcontroleinv, gp_invendu.qtefournip2000, gp_invendu.idparution, gp_invendu.idclient
  • Hash Cond: (gp_invendu.idbiorigine = gp_bi.id)
15. 0.000 0.000 ↓ 0.0

Seq Scan on local140.gp_invendu (cost=0.00..2,068.54 rows=81,803 width=16) (actual rows= loops=)

  • Output: gp_invendu.id, gp_invendu.idparution, gp_invendu.edition, gp_invendu.idclient, gp_invendu.idbr, gp_invendu.qteinvendu, gp_invendu.dateretour, gp_invendu.utilretour, gp_invendu.idbci, gp_invendu.oublie, gp_invendu.r (...)
  • Filter: (gp_invendu.typemvt = ANY ('{0,3}'::integer[]))
16. 0.000 0.000 ↓ 0.0

Hash (cost=40.46..40.46 rows=403 width=20) (actual rows= loops=)

  • Output: gp_bi.daterappel, gp_bi.datecloture, gp_bi.dhcontroleinv, gp_bi.id
17. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on local140.gp_bi (cost=11.40..40.46 rows=403 width=20) (actual rows= loops=)

  • Output: gp_bi.daterappel, gp_bi.datecloture, gp_bi.dhcontroleinv, gp_bi.id
  • Recheck Cond: (gp_bi.daterappel = a0.daterappel)
  • Filter: ((gp_bi.typebi = 0) OR ((gp_bi.typebi = 2) AND (gp_bi.typebdxp2000 = 'J'::bpchar)))
18. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on gp_bi_daterappel_idx (cost=0.00..11.30 rows=403 width=0) (actual rows= loops=)

  • Index Cond: (gp_bi.daterappel = a0.daterappel)
19.          

SubPlan (forAggregate)

20. 0.000 0.000 ↓ 0.0

Aggregate (cost=12.63..12.64 rows=1 width=4) (actual rows= loops=)

  • Output: sum(gp_parutionclient.qtemvt)
21. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on local140.gp_parutionclient (cost=8.61..12.63 rows=1 width=4) (actual rows= loops=)

  • Output: gp_parutionclient.id, gp_parutionclient.idparution, gp_parutionclient.idclient, gp_parutionclient.edition, gp_parutionclient.qtemvt, gp_parutionclient.typemvt, gp_parutionclient.datemvt, gp_parutionclient.idbord, gp_pa (...)
  • Recheck Cond: ((gp_parutionclient.idparution = gp_invendu.idparution) AND (gp_parutionclient.idclient = gp_invendu.idclient))
  • Filter: (gp_parutionclient.typemvt = ANY ('{0,1,2,6,8}'::integer[]))
22. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=8.61..8.61 rows=1 width=0) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on gp_parutionclient_parut_idx (cost=0.00..4.18 rows=4 width=0) (actual rows= loops=)

  • Index Cond: (gp_parutionclient.idparution = gp_invendu.idparution)
24. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on gp_parutionclient_client_idx (cost=0.00..4.18 rows=4 width=0) (actual rows= loops=)

  • Index Cond: (gp_parutionclient.idclient = gp_invendu.idclient)
25. 0.000 0.000 ↓ 0.0

Index Scan using gp_parution_pkey on local140.gp_parution (cost=0.29..8.31 rows=1 width=6) (actual rows= loops=)

  • Output: gp_parution.prix
  • Index Cond: (gp_parution.id = gp_invendu.idparution)
26. 0.000 0.000 ↓ 0.0

Aggregate (cost=12.63..12.64 rows=1 width=4) (actual rows= loops=)

  • Output: sum(gp_parutionclient_1.qtemvt)
27. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on local140.gp_parutionclient gp_parutionclient_1 (cost=8.61..12.63 rows=1 width=4) (actual rows= loops=)

  • Output: gp_parutionclient_1.id, gp_parutionclient_1.idparution, gp_parutionclient_1.idclient, gp_parutionclient_1.edition, gp_parutionclient_1.qtemvt, gp_parutionclient_1.typemvt, gp_parutionclient_1.datemvt, gp_parutionclient (...)
  • Recheck Cond: ((gp_parutionclient_1.idparution = gp_invendu.idparution) AND (gp_parutionclient_1.idclient = gp_invendu.idclient))
  • Filter: (gp_parutionclient_1.typemvt = ANY ('{0,1,2,6,8}'::integer[]))
28. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=8.61..8.61 rows=1 width=0) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on gp_parutionclient_parut_idx (cost=0.00..4.18 rows=4 width=0) (actual rows= loops=)

  • Index Cond: (gp_parutionclient_1.idparution = gp_invendu.idparution)
30. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on gp_parutionclient_client_idx (cost=0.00..4.18 rows=4 width=0) (actual rows= loops=)

  • Index Cond: (gp_parutionclient_1.idclient = gp_invendu.idclient)
31. 0.000 0.000 ↓ 0.0

Index Scan using gp_parution_pkey on local140.gp_parution gp_parution_1 (cost=0.29..8.31 rows=1 width=6) (actual rows= loops=)

  • Output: gp_parution_1.prix
  • Index Cond: (gp_parution_1.id = gp_invendu.idparution)
32. 0.000 0.000 ↓ 0.0

Aggregate (cost=688,658.40..688,658.42 rows=1 width=32) (actual rows= loops=)

  • Output: round(((sum(CASE WHEN CASE WHEN (gp_bi_1.daterappel = COALESCE(gp_bi_1.datecloture, gp_bi_1.daterappel)) THEN ((COALESCE(gp_invendu_1.modecontroleligneinv, 0) = 2) AND (gp_bi_1.dhcontroleinv IS NOT NULL)) ELSE false END THEN ((COALE (...)
33. 0.000 0.000 ↓ 0.0

Result (cost=45.50..2,584.41 rows=16,361 width=32) (actual rows= loops=)

  • Output: gp_bi_1.daterappel, gp_bi_1.datecloture, gp_bi_1.dhcontroleinv, gp_invendu_1.modecontroleligneinv, gp_invendu_1.qtefournip2000, gp_invendu_1.idparution, gp_invendu_1.idclient
  • One-Time Filter: (a0.etatinv = 1)
34. 0.000 0.000 ↓ 0.0

Hash Join (cost=45.50..2,584.41 rows=16,361 width=32) (actual rows= loops=)

  • Output: gp_bi_1.daterappel, gp_bi_1.datecloture, gp_bi_1.dhcontroleinv, gp_invendu_1.modecontroleligneinv, gp_invendu_1.qtefournip2000, gp_invendu_1.idparution, gp_invendu_1.idclient
  • Hash Cond: (gp_invendu_1.idbiorigine = gp_bi_1.id)
35. 0.000 0.000 ↓ 0.0

Seq Scan on local140.gp_invendu gp_invendu_1 (cost=0.00..2,068.54 rows=81,803 width=20) (actual rows= loops=)

  • Output: gp_invendu_1.id, gp_invendu_1.idparution, gp_invendu_1.edition, gp_invendu_1.idclient, gp_invendu_1.idbr, gp_invendu_1.qteinvendu, gp_invendu_1.dateretour, gp_invendu_1.utilretour, gp_invendu_1.idbci, gp_invendu_1. (...)
  • Filter: (gp_invendu_1.typemvt = ANY ('{0,3}'::integer[]))
36. 0.000 0.000 ↓ 0.0

Hash (cost=40.46..40.46 rows=403 width=20) (actual rows= loops=)

  • Output: gp_bi_1.daterappel, gp_bi_1.datecloture, gp_bi_1.dhcontroleinv, gp_bi_1.id
37. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on local140.gp_bi gp_bi_1 (cost=11.40..40.46 rows=403 width=20) (actual rows= loops=)

  • Output: gp_bi_1.daterappel, gp_bi_1.datecloture, gp_bi_1.dhcontroleinv, gp_bi_1.id
  • Recheck Cond: (gp_bi_1.daterappel = a0.daterappel)
  • Filter: ((gp_bi_1.typebi = 0) OR ((gp_bi_1.typebi = 2) AND (gp_bi_1.typebdxp2000 = 'J'::bpchar)))
38. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on gp_bi_daterappel_idx (cost=0.00..11.30 rows=403 width=0) (actual rows= loops=)

  • Index Cond: (gp_bi_1.daterappel = a0.daterappel)
39.          

SubPlan (forAggregate)

40. 0.000 0.000 ↓ 0.0

Aggregate (cost=12.63..12.64 rows=1 width=4) (actual rows= loops=)

  • Output: sum(gp_parutionclient_2.qtemvt)
41. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on local140.gp_parutionclient gp_parutionclient_2 (cost=8.61..12.63 rows=1 width=4) (actual rows= loops=)

  • Output: gp_parutionclient_2.id, gp_parutionclient_2.idparution, gp_parutionclient_2.idclient, gp_parutionclient_2.edition, gp_parutionclient_2.qtemvt, gp_parutionclient_2.typemvt, gp_parutionclient_2.datemvt, gp_parutionclient (...)
  • Recheck Cond: ((gp_parutionclient_2.idparution = gp_invendu_1.idparution) AND (gp_parutionclient_2.idclient = gp_invendu_1.idclient))
  • Filter: (gp_parutionclient_2.typemvt = ANY ('{0,1,2,6,8}'::integer[]))
42. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=8.61..8.61 rows=1 width=0) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on gp_parutionclient_parut_idx (cost=0.00..4.18 rows=4 width=0) (actual rows= loops=)

  • Index Cond: (gp_parutionclient_2.idparution = gp_invendu_1.idparution)
44. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on gp_parutionclient_client_idx (cost=0.00..4.18 rows=4 width=0) (actual rows= loops=)

  • Index Cond: (gp_parutionclient_2.idclient = gp_invendu_1.idclient)
45. 0.000 0.000 ↓ 0.0

Index Scan using gp_parution_pkey on local140.gp_parution gp_parution_2 (cost=0.29..8.31 rows=1 width=6) (actual rows= loops=)

  • Output: gp_parution_2.prix
  • Index Cond: (gp_parution_2.id = gp_invendu_1.idparution)
46. 0.000 0.000 ↓ 0.0

Aggregate (cost=12.63..12.64 rows=1 width=4) (actual rows= loops=)

  • Output: sum(gp_parutionclient_3.qtemvt)
47. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on local140.gp_parutionclient gp_parutionclient_3 (cost=8.61..12.63 rows=1 width=4) (actual rows= loops=)

  • Output: gp_parutionclient_3.id, gp_parutionclient_3.idparution, gp_parutionclient_3.idclient, gp_parutionclient_3.edition, gp_parutionclient_3.qtemvt, gp_parutionclient_3.typemvt, gp_parutionclient_3.datemvt, gp_parutionclient (...)
  • Recheck Cond: ((gp_parutionclient_3.idparution = gp_invendu_1.idparution) AND (gp_parutionclient_3.idclient = gp_invendu_1.idclient))
  • Filter: (gp_parutionclient_3.typemvt = ANY ('{0,1,2,6,8}'::integer[]))
48. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=8.61..8.61 rows=1 width=0) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on gp_parutionclient_parut_idx (cost=0.00..4.18 rows=4 width=0) (actual rows= loops=)

  • Index Cond: (gp_parutionclient_3.idparution = gp_invendu_1.idparution)
50. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on gp_parutionclient_client_idx (cost=0.00..4.18 rows=4 width=0) (actual rows= loops=)

  • Index Cond: (gp_parutionclient_3.idclient = gp_invendu_1.idclient)
51. 0.000 0.000 ↓ 0.0

Index Scan using gp_parution_pkey on local140.gp_parution gp_parution_3 (cost=0.29..8.31 rows=1 width=6) (actual rows= loops=)

  • Output: gp_parution_3.prix
  • Index Cond: (gp_parution_3.id = gp_invendu_1.idparution)