explain.depesz.com

PostgreSQL's explain analyze made readable

Result: X41X

Settings
# exclusive inclusive rows x rows loops node
1. 0.350 546,072.502 ↑ 1.0 2,035 1

Unique (cost=19,026.79..19,042.06 rows=2,035 width=15) (actual time=546,072.058..546,072.502 rows=2,035 loops=1)

  • Buffers: shared hit=296035746 read=60 dirtied=1, temp read=361 written=361
2. 9.787 546,072.152 ↓ 1.0 2,036 1

Sort (cost=19,026.79..19,031.88 rows=2,035 width=15) (actual time=546,072.056..546,072.152 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=296035746 read=60 dirtied=1, temp read=361 written=361
3. 9.004 546,062.365 ↓ 1.0 2,036 1

Hash Join (cost=17,272.1..18,914.96 rows=2,035 width=15) (actual time=73,991.67..546,062.365 rows=2,036 loops=1)

  • Buffers: shared hit=296035746 read=60 dirtied=1, temp read=361 written=361
4. 14,322.315 544,847.250 ↓ 1,647.0 1,647 1

Nested Loop (cost=4,167.65..5,739.27 rows=1 width=39) (actual time=72,785.534..544,847.25 rows=1,647 loops=1)

  • Buffers: shared hit=295891807 read=60 dirtied=1, temp read=361 written=361
5. 9,760.436 16,365.905 ↓ 73,451,290.0 73,451,290 1

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

  • Buffers: shared hit=1148224 read=60 dirtied=1, temp read=361 written=361
6. 30.224 830.429 ↓ 36,094.0 36,094 1

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

  • Buffers: shared hit=390250 read=60 dirtied=1, temp read=361 written=361
7. 35.988 511.819 ↓ 41,198.0 41,198 1

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

  • Buffers: shared hit=225351 read=60 dirtied=1, temp read=361 written=361
8. 108.644 174.706 ↓ 200.1 60,225 1

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

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

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

  • Buffers: shared hit=497
10. 20.564 55.743 ↓ 192.9 100,699 1

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

  • Buffers: shared hit=2595 dirtied=1, temp written=165
11. 35.179 35.179 ↓ 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.179 rows=100,699 loops=1)

  • Filter: ((veh.retired)::integer = 0)
  • Buffers: shared hit=2595 dirtied=1
12. 301.125 301.125 ↑ 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.005..0.005 rows=1 loops=60,225)

  • Index Cond: (inc.vehicleid = edge.ownerid)
  • Filter: ((inc.subtype = '3'::numeric) AND ((inc.retired)::integer = 0))
  • Buffers: shared hit=222259 read=60
13. 288.386 288.386 ↑ 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.007..0.007 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,775.040 5,775.040 ↑ 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.16 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.338 1,206.111 ↑ 1.0 2,035 1

Hash (cost=13,079.01..13,079.01 rows=2,035 width=43) (actual time=1,206.111..1,206.111 rows=2,035 loops=1)

  • Buffers: shared hit=143939
17. 0.458 1,205.773 ↑ 1.0 2,035 1

Hash Join (cost=13,030.02..13,079.01 rows=2,035 width=43) (actual time=1,205.173..1,205.773 rows=2,035 loops=1)

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

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

  • Buffers: shared hit=21
19. 0.290 1,205.154 ↓ 1,646.0 1,646 1

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

  • Buffers: shared hit=143918
20. 0.180 1,204.864 ↓ 1,646.0 1,646 1

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

  • Buffers: shared hit=143918
21. 0.316 1,204.684 ↓ 1,646.0 1,646 1

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

  • Buffers: shared hit=143918
22. 7.196 1,204.368 ↓ 1,646.0 1,646 1

Sort (cost=13,029.99..13,029.99 rows=1 width=75) (actual time=1,204.291..1,204.368 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.437 1,197.172 ↓ 1,646.0 1,646 1

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

  • Buffers: shared hit=143918
24. 1.086 1,188.331 ↓ 1,851.0 1,851 1

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

  • Buffers: shared hit=136511
25. 17.752 1,179.841 ↓ 1,851.0 1,851 1

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

  • Buffers: shared hit=129102
26. 3.972 68.577 ↓ 26,036.0 26,036 1

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

  • Buffers: shared hit=54696
27. 1.714 29.275 ↓ 7,066.0 7,066 1

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

  • Buffers: shared hit=19581
28. 0.859 17.386 ↓ 203.5 2,035 1

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

  • Buffers: shared hit=8187
29. 0.247 0.247 ↑ 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.247 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 : 9.356 ms
Execution time : 546,072.886 ms