explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UzT

Settings
# exclusive inclusive rows x rows loops node
1. 164.049 301,351.597 ↓ 13,760.0 13,760 1

Nested Loop (cost=6,661,137.81..8,708,336.05 rows=1 width=240) (actual time=270,941.465..301,351.597 rows=13,760 loops=1)

  • Buffers: shared hit=898,580 read=3,254,163, temp read=141,535 written=141,533
2. 49.812 301,132.508 ↓ 13,760.0 13,760 1

Nested Loop (cost=6,661,137.54..8,708,333.43 rows=1 width=245) (actual time=270,940.508..301,132.508 rows=13,760 loops=1)

  • Buffers: shared hit=857,300 read=3,254,163, temp read=141,535 written=141,533
3. 45.666 300,848.776 ↓ 13,760.0 13,760 1

Hash Join (cost=6,661,137.11..8,708,331.4 rows=1 width=261) (actual time=270,940.416..300,848.776 rows=13,760 loops=1)

  • Buffers: shared hit=802,236 read=3,254,037, temp read=141,535 written=141,533
4. 51.251 142,672.605 ↓ 13,760.0 13,760 1

Nested Loop (cost=1,350,595.71..3,397,749.58 rows=1 width=203) (actual time=112,808.41..142,672.605 rows=13,760 loops=1)

  • Buffers: shared hit=358,890 read=1,430,482
5. 28,485.002 140,598.634 ↓ 13,760.0 13,760 1

Hash Join (cost=1,350,595.27..3,397,747.58 rows=1 width=189) (actual time=112,802.773..140,598.634 rows=13,760 loops=1)

  • Buffers: shared hit=306,339 read=1,427,894
6. 46,859.304 46,859.304 ↓ 1.0 23,222,151 1

Seq Scan on commission_flux basecom (cost=0..1,387,496.46 rows=22,944,550 width=52) (actual time=1.12..46,859.304 rows=23,222,151 loops=1)

  • Filter: (((basecom.comflx_type_flux)::text = 'PRIM'::text) AND ((basecom.comflx_source)::text = 'AUXIA'::text))
  • Buffers: shared hit=2,010 read=1,020,351
7. 23.409 65,254.328 ↓ 12,103.0 12,103 1

Hash (cost=1,350,595.25..1,350,595.25 rows=1 width=206) (actual time=65,254.328..65,254.328 rows=12,103 loops=1)

  • Buffers: shared hit=304,329 read=407,543
8. 147.057 65,230.919 ↓ 12,103.0 12,103 1

Hash Join (cost=1,272,536.96..1,350,595.25 rows=1 width=206) (actual time=62,916.027..65,230.919 rows=12,103 loops=1)

  • Buffers: shared hit=304,329 read=407,543
9. 24.342 32,316.804 ↓ 356.2 12,466 1

Nested Loop (cost=424,584.06..502,579.51 rows=35 width=193) (actual time=30,033.683..32,316.804 rows=12,466 loops=1)

  • Buffers: shared hit=289,038 read=87,557
10. 55.132 32,192.734 ↓ 356.2 12,466 1

Hash Join (cost=424,583.63..502,508.87 rows=35 width=200) (actual time=30,031.898..32,192.734 rows=12,466 loops=1)

  • Buffers: shared hit=250,926 read=87,556
11. 77.479 20,086.349 ↓ 1.0 49,189 1

Hash Join (cost=148,529.26..226,272.92 rows=48,420 width=38) (actual time=16,760.991..20,086.349 rows=49,189 loops=1)

  • Buffers: shared hit=124,824 read=85,200
12. 2,042.100 16,678.880 ↓ 1.0 49,189 1

Hash Join (cost=109,023..186,403.5 rows=48,420 width=15) (actual time=13,430.957..16,678.88 rows=49,189 loops=1)

  • Buffers: shared hit=17,171 read=85,200
13. 6,960.810 6,960.810 ↓ 1.0 2,615,561 1

Seq Scan on police_etat pe (cost=0..67,093.02 rows=2,614,206 width=10) (actual time=1,360.531..6,960.81 rows=2,615,561 loops=1)

  • Filter: ((pe.source)::text = 'AUXIA'::text)
  • Buffers: shared hit=9,470 read=24,397
14. 52.265 7,675.970 ↑ 1.0 49,189 1

Hash (cost=108,407.1..108,407.1 rows=49,272 width=11) (actual time=7,675.97..7,675.97 rows=49,189 loops=1)

  • Buffers: shared hit=7,701 read=60,803
15. 7,623.705 7,623.705 ↑ 1.0 49,189 1

Seq Scan on police pol_1 (cost=0..108,407.1 rows=49,272 width=11) (actual time=2,320.686..7,623.705 rows=49,189 loops=1)

  • Filter: (((pol_1.source)::text = 'AUXIA'::text) AND ((pol_1.pol_code_produit)::text = 'BDAP'::text))
  • Buffers: shared hit=7,701 read=60,803
16. 5.756 3,329.990 ↓ 6,681.0 6,681 1

Hash (cost=39,506.24..39,506.24 rows=1 width=43) (actual time=3,329.99..3,329.99 rows=6,681 loops=1)

  • Buffers: shared hit=107,653
17. 6.928 3,324.234 ↓ 6,681.0 6,681 1

Subquery Scan on tmp_avenant (cost=39,506.21..39,506.24 rows=1 width=43) (actual time=3,309.096..3,324.234 rows=6,681 loops=1)

  • Buffers: shared hit=107,653
18. 37.911 3,317.306 ↓ 6,681.0 6,681 1

HashAggregate (cost=39,506.21..39,506.23 rows=1 width=16) (actual time=3,309.093..3,317.306 rows=6,681 loops=1)

  • Group Key: avn.avn_source, avn.avn_num_police_interne
  • Buffers: shared hit=107,653
19. 20.281 3,279.395 ↓ 6,872.0 6,872 1

Nested Loop (cost=0.99..39,506.21 rows=1 width=16) (actual time=14.629..3,279.395 rows=6,872 loops=1)

  • Buffers: shared hit=107,653
20. 33.488 1,390.886 ↓ 20.9 6,971 1

Nested Loop (cost=0.43..38,596.29 rows=334 width=22) (actual time=9.35..1,390.886 rows=6,971 loops=1)

  • Buffers: shared hit=72,692
21. 75.848 75.848 ↑ 1.0 18,050 1

Seq Scan on avenant avn (cost=0..570.16 rows=18,050 width=22) (actual time=0.654..75.848 rows=18,050 loops=1)

  • Filter: ((avn.avn_source)::text = 'AUXIA'::text)
  • Buffers: shared hit=344
22. 1,281.550 1,281.550 ↓ 0.0 0 18,050

Index Scan using police_unique on police pol_2 (cost=0.43..2.1 rows=1 width=6) (actual time=0.07..0.071 rows=0 loops=18,050)

  • Index Cond: (((pol_2.source)::text = 'AUXIA'::text) AND (pol_2.police_id = avn.avn_num_police_interne))
  • Filter: ((pol_2.pol_code_produit)::text = 'BDAP'::text)
  • Buffers: shared hit=72,348
23. 1,868.228 1,868.228 ↑ 1.0 1 6,971

Index Scan using etat_courant_source_dossier_fct on etat_courant ec (cost=0.56..2.71 rows=1 width=8) (actual time=0.266..0.268 rows=1 loops=6,971)

  • Index Cond: (((ec.source)::text = 'AUXIA'::text) AND ((ec.etc_dossier)::text = (avn.avn_numero_dossier)::text) AND ((ec.etc_code_fonction)::text = 'JAAVNTP'::text))
  • Filter: (ec.etc_num_etat = 5)
  • Buffers: shared hit=34,961
24. 17.871 12,051.253 ↓ 356.2 12,466 1

Hash (cost=276,053.93..276,053.93 rows=35 width=168) (actual time=12,051.253..12,051.253 rows=12,466 loops=1)

  • Buffers: shared hit=126,102 read=2,356
25. 309.503 12,033.382 ↓ 356.2 12,466 1

Hash Join (cost=265,191.93..276,053.93 rows=35 width=168) (actual time=11,283.229..12,033.382 rows=12,466 loops=1)

  • Buffers: shared hit=126,102 read=2,356
26. 1,047.413 2,112.122 ↓ 1.0 457,447 1

HashAggregate (cost=13,369.38..17,942.82 rows=457,344 width=10) (actual time=1,671.267..2,112.122 rows=457,447 loops=1)

  • Group Key: speassure.police_id
  • Buffers: shared hit=4,067
27. 1,064.709 1,064.709 ↓ 1.0 457,447 1

Seq Scan on speassure speassure (cost=0..9,939.3 rows=457,344 width=10) (actual time=1.369..1,064.709 rows=457,447 loops=1)

  • Filter: ((speassure.source)::text = 'AUXIA'::text)
  • Buffers: shared hit=4,067
28. 20.297 9,611.757 ↓ 356.2 12,466 1

Hash (cost=251,822.12..251,822.12 rows=35 width=104) (actual time=9,611.757..9,611.757 rows=12,466 loops=1)

  • Buffers: shared hit=122,035 read=2,356
29. 26.990 9,591.460 ↓ 356.2 12,466 1

Nested Loop (cost=0.87..251,822.12 rows=35 width=104) (actual time=4,514.374..9,591.46 rows=12,466 loops=1)

  • Buffers: shared hit=122,035 read=2,356
30. 17.794 8,629.520 ↓ 6.6 12,466 1

Nested Loop (cost=0.44..248,031.48 rows=1,894 width=78) (actual time=4,511.849..8,629.52 rows=12,466 loops=1)

  • Buffers: shared hit=72,055 read=2,356
31. 37.930 37.930 ↓ 1.1 63 1

Seq Scan on commission_releve relcp (cost=0..231.28 rows=59 width=17) (actual time=35.126..37.93 rows=63 loops=1)

  • Filter: (((relcp.comrel_source)::text = 'AUXIA'::text) AND (relcp.comrel_date_production = to_number(to_char((('now'::cstring)::date - '1 mon'::interval), 'yyyyMM'::text), '999999'::text)))
  • Buffers: shared hit=91
32. 8,573.796 8,573.796 ↓ 4.6 198 63

Index Scan using comdet_numero_groupe_idx on commission_detail comm (cost=0.44..4,199.57 rows=43 width=61) (actual time=129.514..136.092 rows=198 loops=63)

  • Index Cond: (comm.comdet_numero_groupe = relcp.comrel_numero_releve)
  • Filter: ((comm.comdet_numero_agent = '237'::numeric) AND ((comm.comdet_source)::text = 'AUXIA'::text))
  • Buffers: shared hit=71,964 read=2,356
33. 934.950 934.950 ↑ 1.0 1 12,466

Index Scan using police_unique on police pol (cost=0.43..1.99 rows=1 width=32) (actual time=0.074..0.075 rows=1 loops=12,466)

  • Index Cond: (((pol.source)::text = 'AUXIA'::text) AND (pol.police_id = comm.comdet_police_id))
  • Filter: ((pol.pol_code_produit)::text = 'BDAP'::text)
  • Buffers: shared hit=49,980
34. 99.728 99.728 ↓ 0.0 0 12,466

Index Scan using cloture_unique on cloture cloture (cost=0.43..2.01 rows=1 width=15) (actual time=0.008..0.008 rows=0 loops=12,466)

  • Index Cond: (((cloture.source)::text = (pol.source)::text) AND ((cloture.source)::text = 'AUXIA'::text) AND (cloture.police_id = pol.police_id))
  • Buffers: shared hit=38,112 read=1
35. 15,697.242 32,767.058 ↑ 1.1 11,172,730 1

Hash (cost=602,717.52..602,717.52 rows=12,261,769 width=38) (actual time=32,767.058..32,767.058 rows=11,172,730 loops=1)

  • Buffers: shared hit=15,291 read=319,986
36. 17,069.816 17,069.816 ↓ 1.3 15,725,907 1

Seq Scan on commission_structure scomm (cost=0..602,717.52 rows=12,261,769 width=38) (actual time=0.056..17,069.816 rows=15,725,907 loops=1)

  • Filter: (((scomm.comstr_numero_com_ref = '0'::numeric) OR (scomm.comstr_numero_com_ref IS NULL)) AND ((scomm.comstr_source)::text = 'AUXIA'::text))
  • Buffers: shared hit=15,291 read=319,986
37. 2,022.720 2,022.720 ↑ 1.0 1 13,760

Index Scan using quipol_numero_quittance_idx on quittance quittance (cost=0.44..1.98 rows=1 width=20) (actual time=0.146..0.147 rows=1 loops=13,760)

  • Index Cond: (quittance.quipol_numero_quittance = scomm.comstr_numero_quittance)
  • Filter: ((quittance.quipol_source)::text = 'AUXIA'::text)
  • Buffers: shared hit=52,551 read=2,588
38. 150.691 158,130.505 ↓ 1.1 180,252 1

Hash (cost=5,308,116.46..5,308,116.46 rows=161,662 width=74) (actual time=158,130.505..158,130.505 rows=180,252 loops=1)

  • Buffers: shared hit=443,346 read=1,823,555, temp read=141,535 written=141,533
39. 180.619 157,979.814 ↓ 1.1 180,252 1

Subquery Scan on tmp_detail_quit (cost=5,304,479.07..5,308,116.46 rows=161,662 width=74) (actual time=157,592.323..157,979.814 rows=180,252 loops=1)

  • Buffers: shared hit=443,346 read=1,823,555, temp read=141,535 written=141,533
40. 532.735 157,799.195 ↓ 1.1 180,252 1

HashAggregate (cost=5,304,479.07..5,306,499.84 rows=161,662 width=22) (actual time=157,592.315..157,799.195 rows=180,252 loops=1)

  • Group Key: quitd.dequipol_numero_quittance, quitd.dequipol_code_garantie
  • Buffers: shared hit=443,346 read=1,823,555, temp read=141,535 written=141,533
41. 8,620.331 157,266.460 ↓ 1.2 186,518 1

Hash Join (cost=3,683,785.53..5,302,862.45 rows=161,662 width=22) (actual time=134,405.019..157,266.46 rows=186,518 loops=1)

  • Buffers: shared hit=443,346 read=1,823,555, temp read=141,535 written=141,533
42. 14,454.552 14,454.552 ↓ 1.1 183,006 1

Seq Scan on commission_detail comm_1 (cost=0..668,159.82 rows=161,662 width=20) (actual time=3.465..14,454.552 rows=183,006 loops=1)

  • Filter: (((comm_1.comdet_source)::text = 'AUXIA'::text) AND (comm_1.comdet_numero_agent = '237'::numeric))
  • Buffers: shared hit=14,346 read=383,028
43. 46,726.890 134,191.577 ↑ 1.1 45,250,607 1

Hash (cost=2,503,841.6..2,503,841.6 rows=48,485,653 width=27) (actual time=134,191.577..134,191.577 rows=45,250,607 loops=1)

  • Buffers: shared hit=429,000 read=1,440,527, temp written=141,053
44. 87,464.687 87,464.687 ↑ 1.1 45,250,607 1

Seq Scan on detail_quittance quitd (cost=0..2,503,841.6 rows=48,485,653 width=27) (actual time=2.78..87,464.687 rows=45,250,607 loops=1)

  • Filter: ((quitd.dequipol_source)::text = 'AUXIA'::text)
  • Buffers: shared hit=429,000 read=1,440,527
45. 233.920 233.920 ↑ 1.0 1 13,760

Index Scan using identite_unique on identite identite_adh (cost=0.43..2.01 rows=1 width=22) (actual time=0.017..0.017 rows=1 loops=13,760)

  • Index Cond: (((identite_adh.source)::text = 'AUXIA'::text) AND (identite_adh.ident_id = tmp_detail_quit.assure))
  • Buffers: shared hit=55,064 read=126
46. 55.040 55.040 ↑ 1.0 1 13,760

Index Scan using comagt_code_agent_idx on commission_agent agenp (cost=0.27..2.49 rows=1 width=10) (actual time=0.003..0.004 rows=1 loops=13,760)

  • Index Cond: (agenp.comagt_code_agent = '237'::numeric)
  • Filter: ((agenp.comagt_source)::text = 'AUXIA'::text)
  • Buffers: shared hit=41,280
Planning time : 193.133 ms
Execution time : 301,406.904 ms