explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Wgj9

Settings
# exclusive inclusive rows x rows loops node
1. 0.338 541,906.944 ↑ 1.0 2,035 1

Unique (cost=19,026.8..19,042.06 rows=2,035 width=15) (actual time=541,906.513..541,906.944 rows=2,035 loops=1)

  • Buffers: shared hit=296035806, temp read=361 written=361
2. 8.883 541,906.606 ↓ 1.0 2,036 1

Sort (cost=19,026.8..19,031.88 rows=2,035 width=15) (actual time=541,906.512..541,906.606 rows=2,036 loops=1)

  • Sort Key: lbld.claimnumber, (CASE WHEN (((btrim(driver.lastname) IS NULL) OR (length(btrim(driver.lastname)) <= 0)) AND ((btrim(split_part((con.lastname)::text, ' '::text, 1)) IS NULL) OR (length(btrim(split_part((con.lastname)::text, ' '::text, 1))) <= 0))) THEN NULL::integer ELSE CASE WHEN (btrim(driver.lastname) = btrim(split_part((con.lastname)::text, ' '::text, 1))) THEN 1 ELSE 0 END END)
  • Sort Method: quicksort Memory: 144kB
  • Buffers: shared hit=296035806, temp read=361 written=361
3. 8.446 541,897.723 ↓ 1.0 2,036 1

Hash Join (cost=17,272.1..18,914.96 rows=2,035 width=15) (actual time=73,471.782..541,897.723 rows=2,036 loops=1)

  • Buffers: shared hit=296035806, temp read=361 written=361
4. 10,412.354 540,689.808 ↓ 1,647.0 1,647 1

Nested Loop (cost=4,167.65..5,739.27 rows=1 width=39) (actual time=72,272.3..540,689.808 rows=1,647 loops=1)

  • Buffers: shared hit=295891867, temp read=361 written=361
5. 9,861.147 16,118.424 ↓ 73,451,290.0 73,451,290 1

Nested Loop (cost=4,167.23..5,732.95 rows=1 width=45) (actual time=55.02..16,118.424 rows=73,451,290 loops=1)

  • Buffers: shared hit=1148284, temp read=361 written=361
6. 50.135 770.989 ↓ 36,094.0 36,094 1

Nested Loop (cost=4,167.23..5,671.25 rows=1 width=34) (actual time=55.013..770.989 rows=36,094 loops=1)

  • Buffers: shared hit=390310, temp read=361 written=361
7. 66.449 473.666 ↓ 41,198.0 41,198 1

Nested Loop (cost=4,166.8..5,669.22 rows=1 width=12) (actual time=54.999..473.666 rows=41,198 loops=1)

  • Buffers: shared hit=225411, temp read=361 written=361
8. 101.371 166.317 ↓ 200.1 60,225 1

Hash Join (cost=4,166.38..5,423.74 rows=301 width=18) (actual time=54.979..166.317 rows=60,225 loops=1)

  • Buffers: shared hit=3092, temp read=361 written=361
9. 10.009 10.009 ↑ 1.0 60,225 1

Seq Scan on ccx_edgeownerinpolic edge (cost=0..1,099.25 rows=60,225 width=12) (actual time=0.006..10.009 rows=60,225 loops=1)

  • Buffers: shared hit=497
10. 19.439 54.937 ↓ 192.9 100,699 1

Hash (cost=4,159.86..4,159.86 rows=522 width=6) (actual time=54.937..54.937 rows=100,699 loops=1)

  • Buffers: shared hit=2595, temp written=165
11. 35.498 35.498 ↓ 192.9 100,699 1

Seq Scan on cc_vehicle veh (cost=0..4,159.86 rows=522 width=6) (actual time=0.007..35.498 rows=100,699 loops=1)

  • Filter: ((veh.retired)::integer = 0)
  • Buffers: shared hit=2595
12. 240.900 240.900 ↑ 1.0 1 60,225

Index Scan using idx_inc_vehicleid on cc_incident inc (cost=0.42..0.8 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=60,225)

  • Index Cond: (inc.vehicleid = edge.ownerid)
  • Filter: ((inc.subtype = '3'::numeric) AND ((inc.retired)::integer = 0))
  • Buffers: shared hit=222319
13. 247.188 247.188 ↑ 1.0 1 41,198

Index Scan using cc_contact_pkey on cc_contact con (cost=0.42..2.03 rows=1 width=34) (actual time=0.006..0.006 rows=1 loops=41,198)

  • Index Cond: (con.id = edge.foreignentityid)
  • Filter: ((con.lastname IS NOT NULL) AND ((con.retired)::integer = 0) AND (length(btrim((con.lastname)::text)) > 0))
  • Buffers: shared hit=164899
14. 5,486.288 5,486.288 ↑ 1.0 2,035 36,094

Seq Scan on temptable_s202002031953_runa lbld_1 (cost=0..41.35 rows=2,035 width=11) (actual time=0.002..0.152 rows=2,035 loops=36,094)

  • Buffers: shared hit=757974
15. 514,159.030 514,159.030 ↑ 1.0 1 73,451,290

Index Scan using idx_ccc_claimnumber on cc_claim claim (cost=0.42..6.31 rows=1 width=17) (actual time=0.007..0.007 rows=1 loops=73,451,290)

  • Index Cond: ((claim.claimnumber)::text = (lbld_1.claimnumber)::text)
  • Filter: ((claim.retired)::integer = 0)
  • Buffers: shared hit=294743583
16. 0.286 1,199.469 ↑ 1.0 2,035 1

Hash (cost=13,079.02..13,079.02 rows=2,035 width=43) (actual time=1,199.468..1,199.469 rows=2,035 loops=1)

  • Buffers: shared hit=143939
17. 0.455 1,199.183 ↑ 1.0 2,035 1

Hash Join (cost=13,030.02..13,079.02 rows=2,035 width=43) (actual time=1,198.583..1,199.183 rows=2,035 loops=1)

  • Buffers: shared hit=143939
18. 0.158 0.158 ↑ 1.0 2,035 1

Seq Scan on temptable_s202002031953_runa lbld (cost=0..41.35 rows=2,035 width=11) (actual time=0.008..0.158 rows=2,035 loops=1)

  • Buffers: shared hit=21
19. 0.251 1,198.570 ↓ 1,646.0 1,646 1

Hash (cost=13,030.01..13,030.01 rows=1 width=43) (actual time=1,198.57..1,198.57 rows=1,646 loops=1)

  • Buffers: shared hit=143918
20. 0.191 1,198.319 ↓ 1,646.0 1,646 1

Subquery Scan on driver (cost=13,029.99..13,030.01 rows=1 width=43) (actual time=1,197.74..1,198.319 rows=1,646 loops=1)

  • Buffers: shared hit=143918
21. 0.314 1,198.128 ↓ 1,646.0 1,646 1

Unique (cost=13,029.99..13,030 rows=1 width=75) (actual time=1,197.738..1,198.128 rows=1,646 loops=1)

  • Buffers: shared hit=143918
22. 6.921 1,197.814 ↓ 1,646.0 1,646 1

Sort (cost=13,029.99..13,029.99 rows=1 width=75) (actual time=1,197.737..1,197.814 rows=1,646 loops=1)

  • Sort Key: claim_1.claimnumber, (split_part((con_1.lastname)::text, ' '::text, 1)), (split_part((con_1.lastname)::text, ' '::text, 2))
  • Sort Method: quicksort Memory: 186kB
  • Buffers: shared hit=143918
23. 1.251 1,190.893 ↓ 1,646.0 1,646 1

Nested Loop (cost=2.12..13,029.98 rows=1 width=75) (actual time=0.574..1,190.893 rows=1,646 loops=1)

  • Buffers: shared hit=143918
24. 0.921 1,182.238 ↓ 1,851.0 1,851 1

Nested Loop (cost=1.7..13,026.97 rows=1 width=45) (actual time=0.549..1,182.238 rows=1,851 loops=1)

  • Buffers: shared hit=136511
25. 12.671 1,173.913 ↓ 1,851.0 1,851 1

Nested Loop (cost=1.27..13,020.44 rows=1 width=23) (actual time=0.539..1,173.913 rows=1,851 loops=1)

  • Buffers: shared hit=129102
26. 3.499 67.730 ↓ 26,036.0 26,036 1

Nested Loop (cost=1.27..13,014.62 rows=1 width=29) (actual time=0.043..67.73 rows=26,036 loops=1)

  • Buffers: shared hit=54696
27. 1.567 28.901 ↓ 7,066.0 7,066 1

Nested Loop (cost=0.84..13,006.91 rows=1 width=23) (actual time=0.034..28.901 rows=7,066 loops=1)

  • Buffers: shared hit=19581
28. 0.647 17.159 ↓ 203.5 2,035 1

Nested Loop (cost=0.42..12,898.19 rows=10 width=17) (actual time=0.023..17.159 rows=2,035 loops=1)

  • Buffers: shared hit=8187
29. 0.232 0.232 ↑ 1.0 2,035 1

Seq Scan on temptable_s202002031953_runa lbld_2 (cost=0..41.35 rows=2,035 width=11) (actual time=0.002..0.232 rows=2,035 loops=1)

  • Buffers: shared hit=21
30. 16.280 16.280 ↑ 1.0 1 2,035

Index Scan using idx_ccc_claimnumber on cc_claim claim_1 (cost=0.42..6.31 rows=1 width=17) (actual time=0.008..0.008 rows=1 loops=2,035)

  • Index Cond: ((claim_1.claimnumber)::text = (lbld_2.claimnumber)::text)
  • Filter: ((claim_1.retired)::integer = 0)
  • Buffers: shared hit=8166
31. 10.175 10.175 ↓ 3.0 3 2,035

Index Scan using idx_ccc_claimlid on cc_claimcontact cc (cost=0.42..10.86 rows=1 width=18) (actual time=0.003..0.005 rows=3 loops=2,035)

  • Index Cond: (cc.claimid = claim_1.id)
  • Filter: ((cc.retired)::integer = 0)
  • Buffers: shared hit=11394
32. 35.330 35.330 ↓ 4.0 4 7,066

Index Scan using idx_ccr_ccid on cc_claimcontactrole ccr (cost=0.43..7.7 rows=1 width=18) (actual time=0.003..0.005 rows=4 loops=7,066)

  • Index Cond: (ccr.claimcontactid = cc.id)
  • Filter: ((ccr.retired)::integer = 0)
  • Buffers: shared hit=35115
33. 1,093.512 1,093.512 ↑ 1.0 1 26,036

Seq Scan on cctl_contactrole cr (cost=0..5.81 rows=1 width=6) (actual time=0.004..0.042 rows=1 loops=26,036)

  • Filter: ((lower((cr.typecode)::text) = 'driver'::text) AND ((cr.retired)::integer = 0))
  • Buffers: shared hit=74406
34. 7.404 7.404 ↑ 1.0 1 1,851

Index Scan using cc_contact_pkey on cc_contact con_1 (cost=0.42..6.51 rows=1 width=34) (actual time=0.004..0.004 rows=1 loops=1,851)

  • Index Cond: (con_1.id = cc.contactid)
  • Filter: ((con_1.lastname IS NOT NULL) AND ((con_1.retired)::integer = 0) AND (length(btrim((con_1.lastname)::text)) > 0))
  • Buffers: shared hit=7409
35. 7.404 7.404 ↑ 1.0 1 1,851

Index Scan using cc_incident_pkey on cc_incident inc_1 (cost=0.42..2.94 rows=1 width=6) (actual time=0.004..0.004 rows=1 loops=1,851)

  • Index Cond: (inc_1.id = ccr.incidentid)
  • Filter: ((inc_1.subtype = '3'::numeric) AND (inc_1.vehiclelossparty = '10001'::numeric) AND ((inc_1.retired)::integer = 0))
  • Buffers: shared hit=7407
Planning time : 10.3 ms
Execution time : 541,907.328 ms