explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kSf

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 44,451.671 ↑ 1.0 10 1

Limit (cost=152,449.01..152,449.61 rows=10 width=345) (actual time=44,451.638..44,451.671 rows=10 loops=1)

2. 56.770 44,451.656 ↑ 471,383,162.9 10 1

WindowAgg (cost=152,449.01..283,230,190.29 rows=4,713,831,629 width=345) (actual time=44,451.637..44,451.656 rows=10 loops=1)

3. 40.853 44,394.886 ↑ 228,715.8 20,610 1

Merge Left Join (cost=152,449.01..82,892,346.05 rows=4,713,831,629 width=122) (actual time=2,533.184..44,394.886 rows=20,610 loops=1)

  • Merge Cond: (latestownedevents.vin = event_1.vin)
  • Join Filter: (latestownedevents.dealership_id = event_1.dealership_id)
4. 43.835 43,869.861 ↑ 576.4 20,610 1

Merge Left Join (cost=107,235.76..323,625.86 rows=11,878,861 width=116) (actual time=2,080.366..43,869.861 rows=20,610 loops=1)

  • Merge Cond: (latestownedevents.vin = e3.vin)
  • Join Filter: (latestownedevents.dealership_id = e3.dealership_id)
5. 89.313 43,285.514 ↑ 2.2 20,610 1

Merge Left Join (cost=73,397.45..80,871.21 rows=44,742 width=104) (actual time=1,577.544..43,285.514 rows=20,610 loops=1)

  • Merge Cond: (latestownedevents.vin = e1.vin)
  • Join Filter: (latestownedevents.dealership_id = e1.dealership_id)
6. 29,350.061 42,595.396 ↓ 112.0 20,610 1

Nested Loop Left Join (cost=40,789.46..46,630.49 rows=184 width=67) (actual time=1,036.206..42,595.396 rows=20,610 loops=1)

  • Join Filter: ((latestownedevents.dealership_id = proposals.dealership_id) AND (latestownedevents.vin = proposals.curr_veh_vin))
  • Rows Removed by Join Filter: 205312584
7. 16.748 1,744.955 ↓ 4,122.0 20,610 1

Nested Loop Left Join (cost=36,318.50..41,349.38 rows=5 width=59) (actual time=983.608..1,744.955 rows=20,610 loops=1)

  • Join Filter: (((CASE WHEN (max(latestownedevents.source_detailed) FILTER (WHERE (latestownedevents.row_number = 1)) = 0) THEN NULL::integer WHEN (max(latestownedevents.source_detailed) FILTER (WHERE (latestownedevents.source = 1)) IS NOT NULL) THEN CASE WHEN ((max(latestownedevents.event_tstamp) FILTER (WHERE ((latestownedevents.source > 1) AND latestownedevents.oc_flag)) IS NOT NULL) AND (max(latestownedevents.event_tstamp) FILTER (WHERE ((latestownedevents.source > 1) AND latestownedevents.oc_flag)) >= max(latestownedevents.event_tstamp) FILTER (WHERE latestownedevents.oc_flag)) AND (max((latestownedevents.event_tstamp)::date) FILTER (WHERE ((latestownedevents.source > 1) AND latestownedevents.oc_flag)) > max((latestownedevents.event_tstamp)::date) FILTER (WHERE (latestownedevents.source = 1))) AND CASE WHEN (max((latestownedevents.event_tstamp)::date) FILTER (WHERE ((latestownedevents.source = 1) AND latestownedevents.oc_flag)) IS NOT NULL) THEN ((max((latestownedevents.event_tstamp)::date) FILTER (WHERE ((latestownedevents.source > 1) AND latestownedevents.oc_flag)) - max((latestownedevents.event_tstamp)::date) FILTER (WHERE ((latestownedevents.source = 1) AND latestownedevents.oc_flag))) > 90) ELSE true END) THEN max(latestownedevents.source_detailed) FILTER (WHERE (latestownedevents.row_number = 1)) ELSE 1 END ELSE max(latestownedevents.source_detailed) FILTER (WHERE (latestownedevents.row_number = 1)) END) = 2) AND (custreads.dealership_id = latestownedevents.dealership_id) AND (custreads.customer_id = ((CASE WHEN (max(latestownedevents.source_detailed) FILTER (WHERE (latestownedevents.row_number = 1)) = 0) THEN NULL::text WHEN (max(latestownedevents.source_detailed) FILTER (WHERE (latestownedevents.source = 1)) IS NOT NULL) THEN CASE WHEN ((max(latestownedevents.event_tstamp) FILTER (WHERE ((latestownedevents.source > 1) AND latestownedevents.oc_flag)) IS NOT NULL) AND (max(latestownedevents.event_tstamp) FILTER (WHERE ((latestownedevents.source > 1) AND latestownedevents.oc_flag)) >= max(latestownedevents.event_tstamp) FILTER (WHERE latestownedevents.oc_flag)) AND (max((latestownedevents.event_tstamp)::date) FILTER (WHERE ((latestownedevents.source > 1) AND latestownedevents.oc_flag)) > max((latestownedevents.event_tstamp)::date) FILTER (WHERE (latestownedevents.source = 1))) AND CASE WHEN (max((latestownedevents.event_tstamp)::date) FILTER (WHERE ((latestownedevents.source = 1) AND latestownedevents.oc_flag)) IS NOT NULL) THEN ((max((latestownedevents.event_tstamp)::date) FILTER (WHERE ((latestownedevents.source > 1) AND latestownedevents.oc_flag)) - max((latestownedevents.event_tstamp)::date) FILTER (WHERE ((latestownedevents.source = 1) AND latestownedevents.oc_flag))) > 90) ELSE true END) THEN max((latestownedevents.customer_id)::text) FILTER (WHERE (latestownedevents.row_number = 1)) ELSE max((latestownedevents.customer_id)::text) FILTER (WHERE (latestownedevents.source = 1)) END ELSE max((latestownedevents.customer_id)::text) FILTER (WHERE (latestownedevents.row_number = 1)) END)::uuid)))
8. 69.338 1,728.207 ↓ 4,122.0 20,610 1

Nested Loop Left Join (cost=36,318.50..41,348.20 rows=5 width=49) (actual time=983.590..1,728.207 rows=20,610 loops=1)

  • Join Filter: ((CASE WHEN (max(latestownedevents.source_detailed) FILTER (WHERE (latestownedevents.row_number = 1)) = 0) THEN NULL::integer WHEN (max(latestownedevents.source_detailed) FILTER (WHERE (latestownedevents.source = 1)) IS NOT NULL) THEN CASE WHEN ((max(latestownedevents.event_tstamp) FILTER (WHERE ((latestownedevents.source > 1) AND latestownedevents.oc_flag)) IS NOT NULL) AND (max(latestownedevents.event_tstamp) FILTER (WHERE ((latestownedevents.source > 1) AND latestownedevents.oc_flag)) >= max(latestownedevents.event_tstamp) FILTER (WHERE latestownedevents.oc_flag)) AND (max((latestownedevents.event_tstamp)::date) FILTER (WHERE ((latestownedevents.source > 1) AND latestownedevents.oc_flag)) > max((latestownedevents.event_tstamp)::date) FILTER (WHERE (latestownedevents.source = 1))) AND CASE WHEN (max((latestownedevents.event_tstamp)::date) FILTER (WHERE ((latestownedevents.source = 1) AND latestownedevents.oc_flag)) IS NOT NULL) THEN ((max((latestownedevents.event_tstamp)::date) FILTER (WHERE ((latestownedevents.source > 1) AND latestownedevents.oc_flag)) - max((latestownedevents.event_tstamp)::date) FILTER (WHERE ((latestownedevents.source = 1) AND latestownedevents.oc_flag))) > 90) ELSE true END) THEN max(latestownedevents.source_detailed) FILTER (WHERE (latestownedevents.row_number = 1)) ELSE 1 END ELSE max(latestownedevents.source_detailed) FILTER (WHERE (latestownedevents.row_number = 1)) END) = 1)
9. 280.522 1,452.769 ↓ 4,122.0 20,610 1

GroupAggregate (cost=36,318.07..41,305.85 rows=5 width=41) (actual time=983.556..1,452.769 rows=20,610 loops=1)

  • Group Key: latestownedevents.dealership_id, latestownedevents.vin
  • Filter: (CASE WHEN ((CASE WHEN (max(latestownedevents.source_detailed) FILTER (WHERE (latestownedevents.row_number = 1)) = 0) THEN NULL::text WHEN (max(latestownedevents.source_detailed) FILTER (WHERE (latestownedevents.source = 1)) IS NOT NULL) THEN CASE WHEN ((max(latestownedevents.event_tstamp) FILTER (WHERE ((latestownedevents.source > 1) AND latestownedevents.oc_flag)) IS NOT NULL) AND (max(latestownedevents.event_tstamp) FILTER (WHERE ((latestownedevents.source > 1) AND latestownedevents.oc_flag)) >= max(latestownedevents.event_tstamp) FILTER (WHERE latestownedevents.oc_flag)) AND (max((latestownedevents.event_tstamp)::date) FILTER (WHERE ((latestownedevents.source > 1) AND latestownedevents.oc_flag)) > max((latestownedevents.event_tstamp)::date) FILTER (WHERE (latestownedevents.source = 1))) AND CASE WHEN (max((latestownedevents.event_tstamp)::date) FILTER (WHERE ((latestownedevents.source = 1) AND latestownedevents.oc_flag)) IS NOT NULL) THEN ((max((latestownedevents.event_tstamp)::date) FILTER (WHERE ((latestownedevents.source > 1) AND latestownedevents.oc_flag)) - max((latestownedevents.event_tstamp)::date) FILTER (WHERE ((latestownedevents.source = 1) AND latestownedevents.oc_flag))) > 90) ELSE true END) THEN max((latestownedevents.customer_id)::text) FILTER (WHERE (latestownedevents.row_number = 1)) ELSE max((latestownedevents.customer_id)::text) FILTER (WHERE (latestownedevents.source = 1)) END ELSE max((latestownedevents.customer_id)::text) FILTER (WHERE (latestownedevents.row_number = 1)) END)::uuid IS NOT NULL) THEN 'true'::text ELSE 'false'::text END ~~* 'TRUE'::text)
  • Rows Removed by Filter: 5070
10. 13.509 1,172.247 ↓ 3.2 33,276 1

Subquery Scan on latestownedevents (cost=36,318.07..38,559.92 rows=10,410 width=62) (actual time=983.490..1,172.247 rows=33,276 loops=1)

  • Filter: ((latestownedevents.null_row_number IS NULL) OR (latestownedevents.row_number <= latestownedevents.null_row_number))
  • Rows Removed by Filter: 1064
11. 98.487 1,158.738 ↓ 1.1 34,340 1

WindowAgg (cost=36,318.07..38,173.39 rows=30,922 width=70) (actual time=983.487..1,158.738 rows=34,340 loops=1)

12. 68.379 1,060.251 ↓ 1.1 34,340 1

WindowAgg (cost=36,318.07..37,245.73 rows=30,922 width=70) (actual time=983.474..1,060.251 rows=34,340 loops=1)

13. 177.414 991.872 ↓ 1.1 34,340 1

Sort (cost=36,318.07..36,395.38 rows=30,922 width=62) (actual time=983.457..991.872 rows=34,340 loops=1)

  • Sort Key: latestownedsourceevents.vin, latestownedsourceevents.event_tstamp DESC, latestownedsourceevents.rih_tstamp DESC
  • Sort Method: quicksort Memory: 6365kB
14. 7.403 814.458 ↓ 1.1 34,340 1

Subquery Scan on latestownedsourceevents (cost=33,393.42..34,011.86 rows=30,922 width=62) (actual time=772.658..814.458 rows=34,340 loops=1)

15. 23.627 807.055 ↓ 1.1 34,340 1

Unique (cost=33,393.42..33,702.64 rows=30,922 width=62) (actual time=772.653..807.055 rows=34,340 loops=1)

16. 585.091 783.428 ↓ 2.6 79,228 1

Sort (cost=33,393.42..33,470.72 rows=30,922 width=62) (actual time=772.650..783.428 rows=79,228 loops=1)

  • Sort Key: event.vin, (LEAST(COALESCE(event.event_source, 0), 2)), event.ownership_change_flag, event.event_tstamp DESC, event.rih_tstamp DESC
  • Sort Method: quicksort Memory: 14214kB
17. 60.393 198.337 ↓ 2.6 79,228 1

Hash Left Join (cost=6,985.16..31,087.20 rows=30,922 width=62) (actual time=33.560..198.337 rows=79,228 loops=1)

  • Hash Cond: ((event.dealership_id = vttoc.dealership_id) AND (event.event_id = vttoc.event_id))
  • Filter: CASE WHEN (vttoc.event_id IS NOT NULL) THEN "Xtream"."fnIsVTTUnique"(event.dealership_id, event.vin, (event.event_tstamp)::date, false) ELSE true END
  • Rows Removed by Filter: 37
18. 105.430 120.380 ↓ 1.3 79,265 1

Bitmap Heap Scan on "Event" event (cost=5,928.03..29,436.18 rows=61,843 width=74) (actual time=15.976..120.380 rows=79,265 loops=1)

  • Recheck Cond: (dealership_id = 2)
  • Filter: ((vin IS NOT NULL) AND (event_type <> 'VTTRequest'::text) AND (event_type <> 'VTTReceive'::text) AND (event_type <> 'Inspection'::text) AND ((event_source = 1) OR ownership_change_flag) AND (COALESCE(vin, ''::text) <> ''::text))
  • Rows Removed by Filter: 124118
  • Heap Blocks: exact=6362
19. 14.950 14.950 ↓ 1.0 203,383 1

Bitmap Index Scan on "Event_customer_id_idx" (cost=0.00..5,912.57 rows=202,686 width=0) (actual time=14.950..14.950 rows=203,383 loops=1)

  • Index Cond: (dealership_id = 2)
20. 0.145 17.564 ↑ 4.5 527 1

Hash (cost=1,021.84..1,021.84 rows=2,353 width=20) (actual time=17.564..17.564 rows=527 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 59kB
21. 15.398 17.419 ↑ 4.5 527 1

Bitmap Heap Scan on "OwnershipChange" vttoc (cost=361.28..1,021.84 rows=2,353 width=20) (actual time=5.645..17.419 rows=527 loops=1)

  • Recheck Cond: (dealership_id = 2)
  • Filter: (code = 6)
  • Rows Removed by Filter: 12144
  • Heap Blocks: exact=160
22. 2.021 2.021 ↑ 1.0 12,671 1

Bitmap Index Scan on "OwnershipChange_to_Event_idx" (cost=0.00..360.69 rows=12,837 width=0) (actual time=2.021..2.021 rows=12,671 loops=1)

  • Index Cond: (dealership_id = 2)
23. 206.100 206.100 ↑ 1.0 1 20,610

Index Scan using "idx_CustomerDMS_Lookup" on "CustomerDMS" custdms (cost=0.42..8.45 rows=1 width=28) (actual time=0.010..0.010 rows=1 loops=20,610)

  • Index Cond: ((dealership_id = latestownedevents.dealership_id) AND (dealership_id = 2) AND (customer_id = ((CASE WHEN (max(latestownedevents.source_detailed) FILTER (WHERE (latestownedevents.row_number = 1)) = 0) THEN NULL::text WHEN (max(latestownedevents.source_detailed) FILTER (WHERE (latestownedevents.source = 1)) IS NOT NULL) THEN CASE WHEN ((max(latestownedevents.event_tstamp) FILTER (WHERE ((latestownedevents.source > 1) AND latestownedevents.oc_flag)) IS NOT NULL) AND (max(latestownedevents.event_tstamp) FILTER (WHERE ((latestownedevents.source > 1) AND latestownedevents.oc_flag)) >= max(latestownedevents.event_tstamp) FILTER (WHERE latestownedevents.oc_flag)) AND (max((latestownedevents.event_tstamp)::date) FILTER (WHERE ((latestownedevents.source > 1) AND latestownedevents.oc_flag)) > max((latestownedevents.event_tstamp)::date) FILTER (WHERE (latestownedevents.source = 1))) AND CASE WHEN (max((latestownedevents.event_tstamp)::date) FILTER (WHERE ((latestownedevents.source = 1) AND latestownedevents.oc_flag)) IS NOT NULL) THEN ((max((latestownedevents.event_tstamp)::date) FILTER (WHERE ((latestownedevents.source > 1) AND latestownedevents.oc_flag)) - max((latestownedevents.event_tstamp)::date) FILTER (WHERE ((latestownedevents.source = 1) AND latestownedevents.oc_flag))) > 90) ELSE true END) THEN max((latestownedevents.customer_id)::text) FILTER (WHERE (latestownedevents.row_number = 1)) ELSE max((latestownedevents.customer_id)::text) FILTER (WHERE (latestownedevents.source = 1)) END ELSE max((latestownedevents.customer_id)::text) FILTER (WHERE (latestownedevents.row_number = 1)) END)::uuid)))
24. 0.000 0.000 ↓ 0.0 0 20,610

Materialize (cost=0.00..1.08 rows=1 width=30) (actual time=0.000..0.000 rows=0 loops=20,610)

25. 0.015 0.015 ↓ 0.0 0 1

Seq Scan on "CustomerReads" custreads (cost=0.00..1.07 rows=1 width=30) (actual time=0.014..0.015 rows=0 loops=1)

  • Filter: (dealership_id = 2)
  • Rows Removed by Filter: 6
26. 11,453.038 11,500.380 ↓ 1.4 9,963 20,610

Materialize (cost=4,470.96..4,655.08 rows=7,365 width=29) (actual time=0.002..0.558 rows=9,963 loops=20,610)

27. 1.835 47.342 ↓ 2.3 16,851 1

Subquery Scan on proposals (cost=4,470.96..4,618.26 rows=7,365 width=29) (actual time=41.780..47.342 rows=16,851 loops=1)

28. 15.209 45.507 ↓ 2.3 16,851 1

HashAggregate (cost=4,470.96..4,544.61 rows=7,365 width=29) (actual time=41.778..45.507 rows=16,851 loops=1)

  • Group Key: proposalbase.curr_veh_vin, proposalbase.dealership_id
29. 7.898 30.298 ↓ 3.2 23,580 1

Hash Join (cost=2,636.78..4,415.72 rows=7,365 width=37) (actual time=15.976..30.298 rows=23,580 loops=1)

  • Hash Cond: (proposal.proposal_base_id = proposalbase.proposal_base_id)
30. 6.507 6.507 ↑ 1.0 23,580 1

Index Only Scan using "fki_Proposal_dealership_proposal_base_id_fkey" on "Proposal" proposal (cost=0.42..1,716.37 rows=23,997 width=36) (actual time=0.035..6.507 rows=23,580 loops=1)

  • Index Cond: (dealership_id = 2)
  • Heap Fetches: 0
31. 4.022 15.893 ↓ 1.0 16,851 1

Hash (cost=2,426.32..2,426.32 rows=16,803 width=37) (actual time=15.893..15.893 rows=16,851 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1441kB
32. 11.871 11.871 ↓ 1.0 16,851 1

Seq Scan on "ProposalBase" proposalbase (cost=0.00..2,426.32 rows=16,803 width=37) (actual time=0.015..11.871 rows=16,851 loops=1)

  • Filter: (dealership_id = 2)
  • Rows Removed by Filter: 37895
33. 8.645 600.805 ↑ 2.3 21,080 1

Materialize (cost=32,607.99..33,559.77 rows=48,607 width=58) (actual time=541.326..600.805 rows=21,080 loops=1)

34. 31.105 592.160 ↑ 2.3 21,080 1

Unique (cost=32,607.99..32,952.18 rows=48,607 width=78) (actual time=541.322..592.160 rows=21,080 loops=1)

35. 465.495 561.055 ↑ 1.1 61,338 1

Sort (cost=32,607.99..32,780.09 rows=68,839 width=78) (actual time=541.320..561.055 rows=61,338 loops=1)

  • Sort Key: e1.vin, (LEAST(COALESCE(e1.event_source, 1), 2)), e1.event_tstamp DESC, e1.rih_tstamp DESC
  • Sort Method: quicksort Memory: 10363kB
36. 80.557 95.560 ↑ 1.1 61,338 1

Bitmap Heap Scan on "Event" e1 (cost=5,929.78..27,076.45 rows=68,839 width=78) (actual time=16.023..95.560 rows=61,338 loops=1)

  • Recheck Cond: (dealership_id = 2)
  • Filter: (year IS NOT NULL)
  • Rows Removed by Filter: 142045
  • Heap Blocks: exact=6362
37. 15.003 15.003 ↓ 1.0 203,383 1

Bitmap Index Scan on "Event_customer_id_idx" (cost=0.00..5,912.57 rows=202,686 width=0) (actual time=15.003..15.003 rows=203,383 loops=1)

  • Index Cond: (dealership_id = 2)
38. 6.260 540.512 ↑ 2.6 20,642 1

Materialize (cost=33,838.31..34,896.95 rows=53,099 width=33) (actual time=502.811..540.512 rows=20,642 loops=1)

39. 16.009 534.252 ↑ 2.6 20,642 1

Unique (cost=33,838.31..34,233.21 rows=53,099 width=41) (actual time=502.796..534.252 rows=20,642 loops=1)

40. 425.343 518.243 ↑ 1.2 64,070 1

Sort (cost=33,838.31..34,035.76 rows=78,980 width=41) (actual time=502.793..518.243 rows=64,070 loops=1)

  • Sort Key: e3.vin, e3.event_tstamp DESC, e3.rih_tstamp DESC
  • Sort Method: quicksort Memory: 6542kB
41. 78.150 92.900 ↑ 1.2 64,070 1

Bitmap Heap Scan on "Event" e3 (cost=5,932.32..27,413.61 rows=78,980 width=41) (actual time=17.963..92.900 rows=64,070 loops=1)

  • Recheck Cond: (dealership_id = 2)
  • Filter: ((odometer IS NOT NULL) AND (event_type <> 'Appointment'::text))
  • Rows Removed by Filter: 139313
  • Heap Blocks: exact=6362
42. 14.750 14.750 ↓ 1.0 203,383 1

Bitmap Index Scan on "Event_customer_id_idx" (cost=0.00..5,912.57 rows=202,686 width=0) (actual time=14.750..14.750 rows=203,383 loops=1)

  • Index Cond: (dealership_id = 2)
43. 3.805 484.172 ↑ 4.4 18,109 1

Materialize (cost=45,213.25..47,213.67 rows=79,365 width=27) (actual time=452.807..484.172 rows=18,109 loops=1)

44. 13.395 480.367 ↑ 4.4 18,109 1

Unique (cost=45,213.25..46,221.61 rows=79,365 width=43) (actual time=452.795..480.367 rows=18,109 loops=1)

45. 369.860 466.972 ↑ 3.6 56,296 1

Sort (cost=45,213.25..45,717.43 rows=201,672 width=43) (actual time=452.792..466.972 rows=56,296 loops=1)

  • Sort Key: event_1.vin, event_1.event_tstamp DESC, event_1.rih_tstamp DESC
  • Sort Method: quicksort Memory: 5935kB
46. 82.393 97.112 ↑ 3.6 56,296 1

Bitmap Heap Scan on "Event" event_1 (cost=5,962.99..27,444.28 rows=201,672 width=43) (actual time=15.738..97.112 rows=56,296 loops=1)

  • Recheck Cond: (dealership_id = 2)
  • Filter: (COALESCE(reynolds_style_id, ''::text) <> ''::text)
  • Rows Removed by Filter: 147087
  • Heap Blocks: exact=6362
47. 14.719 14.719 ↓ 1.0 203,383 1

Bitmap Index Scan on "Event_customer_id_idx" (cost=0.00..5,912.57 rows=202,686 width=0) (actual time=14.719..14.719 rows=203,383 loops=1)

  • Index Cond: (dealership_id = 2)
Planning time : 4.223 ms
Execution time : 44,453.585 ms