explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hfEv : TEST

Settings
# exclusive inclusive rows x rows loops node
1. 36.048 150,749.072 ↓ 485.0 485 1

Nested Loop (cost=1,359.28..113,420.81 rows=1 width=693) (actual time=3,842.939..150,749.072 rows=485 loops=1)

  • Join Filter: (svc_site.site_client_id = sc.site_client_id)
  • Rows Removed by Join Filter: 231345
  • Buffers: shared hit=74990273 read=8142 dirtied=26
2. 1.535 1,947.519 ↓ 485.0 485 1

Nested Loop Left Join (cost=1.43..17.53 rows=1 width=30) (actual time=61.661..1,947.519 rows=485 loops=1)

  • Buffers: shared hit=3511 read=321
3. 4.011 1,945.499 ↓ 485.0 485 1

Nested Loop Left Join (cost=1.15..17.23 rows=1 width=27) (actual time=61.634..1,945.499 rows=485 loops=1)

  • Join Filter: (art_site.article_id = svc.article_id)
  • Buffers: shared hit=3489 read=321
4. 3.522 1,908.023 ↓ 485.0 485 1

Nested Loop (cost=0.85..16.90 rows=1 width=30) (actual time=34.442..1,908.023 rows=485 loops=1)

  • Buffers: shared hit=2037 read=318
5. 363.656 363.656 ↓ 485.0 485 1

Index Scan using svccmd_projet_art_id on svccmd svc (cost=0.43..8.45 rows=1 width=8) (actual time=20.517..363.656 rows=485 loops=1)

  • Index Cond: ((projet_id = 95296) AND (article_id = 59948))
  • Buffers: shared hit=280 read=134
6. 1,540.845 1,540.845 ↑ 1.0 1 485

Index Scan using f on svccmd_site svc_site (cost=0.43..8.45 rows=1 width=26) (actual time=3.163..3.177 rows=1 loops=485)

  • Index Cond: (svccmd_id = svc.svccmd_id)
  • Buffers: shared hit=1757 read=184
7. 33.465 33.465 ↑ 1.0 1 485

Index Scan using art_site_pkey on art_site (cost=0.29..0.31 rows=1 width=19) (actual time=0.069..0.069 rows=1 loops=485)

  • Index Cond: ((article_id = 59948) AND ((svc_site.code_site)::text = (code_site)::text))
  • Buffers: shared hit=1452 read=3
8. 0.485 0.485 ↓ 0.0 0 485

Index Scan using site_client_cplt_pkey on site_client_cplt cplt (cost=0.28..0.30 rows=1 width=11) (actual time=0.001..0.001 rows=0 loops=485)

  • Index Cond: (svc_site.cplt_adr_id = cplt_adr_id)
  • Buffers: shared hit=22
9. 2,744.615 148,765.505 ↑ 38.4 478 485

Nested Loop (cost=1,357.86..113,173.79 rows=18,359 width=390) (actual time=10.172..306.733 rows=478 loops=485)

  • Join Filter: (((p.vm_id & sc.vm_id) <> B'0000000000000000000000000000000000000000000000000000000000000000'::bit(64)) OR (p.vm_id = B'0000000000000000000000000000000000000000000000000000000000000000'::bit(64)))
  • Rows Removed by Join Filter: 16972
  • Buffers: shared hit=74986762 read=7821 dirtied=26
10. 7.275 7.275 ↑ 1.0 1 485

Index Scan using projet_pkey on projet p (cost=0.42..8.44 rows=1 width=17) (actual time=0.012..0.015 rows=1 loops=485)

  • Index Cond: (projet_id = 95296)
  • Filter: (stg_id = 11515)
  • Buffers: shared hit=1940
11. 8,934.185 146,013.615 ↑ 1.1 17,450 485

Gather (cost=1,357.44..112,842.46 rows=18,451 width=407) (actual time=10.153..301.059 rows=17,450 loops=485)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=74984822 read=7821 dirtied=26
12. 2,854.225 137,079.430 ↑ 1.3 5,817 1,455

Hash Left Join (cost=357.44..109,997.36 rows=7,688 width=407) (actual time=0.953..282.638 rows=5,817 loops=1,455)

  • Hash Cond: ("substring"((COALESCE(c.address_zipcode, i.code_postal, '000'::character varying))::text, 1, 2) = lpad((gtc.departement)::text, 2, '0'::text))
  • Buffers: shared hit=74984822 read=7821 dirtied=26
13. 95,910.205 134,169.915 ↑ 1.3 5,817 1,455

Nested Loop Left Join (cost=352.28..109,907.25 rows=7,688 width=407) (actual time=0.746..276.639 rows=5,817 loops=1,455)

  • Join Filter: ("substring"((COALESCE(c.address_zipcode, i.code_postal, '000'::character varying))::text, 1, length((dui.num_depart_txt)::text)) = (dui.num_depart_txt)::text)
  • Rows Removed by Join Filter: 575859
  • Buffers: shared hit=74936322 read=7818 dirtied=26
14. 15,689.750 38,255.830 ↑ 1.3 5,817 1,455

Nested Loop Left Join (cost=352.28..81,077.25 rows=7,688 width=407) (actual time=0.699..78.878 rows=5,817 loops=1,455)

  • Buffers: shared hit=66473073 read=7817 dirtied=26
15. 19,232.190 22,563.655 ↑ 1.3 5,816 1,455

Nested Loop Left Join (cost=351.85..58,213.22 rows=7,688 width=220) (actual time=0.585..46.523 rows=5,816 loops=1,455)

  • Buffers: shared hit=32689429 read=6361 dirtied=25
16. 2,758.680 3,329.040 ↑ 1.3 5,816 1,455

Parallel Bitmap Heap Scan on site_client sc (cost=351.42..23,411.72 rows=7,688 width=52) (actual time=0.523..6.864 rows=5,816 loops=1,455)

  • Recheck Cond: (stg_id = 11515)
  • Heap Blocks: exact=314717
  • Buffers: shared hit=787044 read=1566 dirtied=24
17. 570.360 570.360 ↑ 1.1 17,449 485

Bitmap Index Scan on site_client_stg_id (cost=0.00..346.81 rows=18,451 width=0) (actual time=1.176..1.176 rows=17,449 loops=485)

  • Index Cond: (stg_id = 11515)
  • Buffers: shared hit=26136 read=54
18. 2.425 2.425 ↑ 1.0 1 8,462,765

Index Scan using pkey_site_intervention on site_intervention c (cost=0.43..4.53 rows=1 width=168) (actual time=0.005..0.005 rows=1 loops=8,462,765)

  • Index Cond: (coresiteid = sc.site_id)
  • Buffers: shared hit=31902385 read=4795 dirtied=1
19. 2.425 2.425 ↑ 1.0 1 8,462,765

Index Scan using site_import_site_client_id on site_import i (cost=0.42..2.96 rows=1 width=191) (actual time=0.004..0.005 rows=1 loops=8,462,765)

  • Index Cond: (sc.site_client_id = site_client_id)
  • Buffers: shared hit=33783644 read=1456 dirtied=1
20. 3.880 3.880 ↑ 1.0 100 8,463,250

Seq Scan on depart_ui dui (cost=0.00..2.00 rows=100 width=21) (actual time=0.002..0.008 rows=100 loops=8,463,250)

  • Buffers: shared hit=8463249 read=1
21. 16.490 55.290 ↑ 1.0 96 971

Hash (cost=3.96..3.96 rows=96 width=2) (actual time=0.114..0.114 rows=96 loops=971)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=2910 read=3
22. 38.800 38.800 ↑ 1.0 96 971

Seq Scan on gtc (cost=0.00..3.96 rows=96 width=2) (actual time=0.033..0.080 rows=96 loops=971)

  • Buffers: shared hit=2910 read=3
Planning time : 298.294 ms
Execution time : 150,760.236 ms