explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TwK1

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=11,443,092.65..11,446,795.37 rows=1,481,089 width=1,447) (actual rows= loops=)

  • Sort Key: (LEAST(GREATEST(asset.updated_at, asset.photo_count_updated_at, listing.updated_at, titles.updated_at), now())), asset.id
2. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=864,968.45..9,694,490.15 rows=1,481,089 width=1,447) (actual rows= loops=)

  • Hash Cond: (ec_map.exterior_color_id = ec.id)
3. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=864,966.97..1,496,455.27 rows=1,481,089 width=815) (actual rows= loops=)

  • Hash Cond: (vehicle.exterior_color_map_id = ec_map.id)
4. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=864,901.85..1,492,493.91 rows=1,481,089 width=809) (actual rows= loops=)

  • Hash Cond: (makes_map.make_id = makes.id)
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=864,897.78..1,488,598.27 rows=1,481,089 width=805) (actual rows= loops=)

  • Hash Cond: (vehicle.make_map_id = makes_map.id)
6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=864,620.81..1,484,431.52 rows=1,481,089 width=799) (actual rows= loops=)

  • Hash Cond: (vehicle.model_map_id = models_map.id)
7. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=857,870.16..1,177,859.93 rows=1,481,089 width=791) (actual rows= loops=)

  • Merge Cond: (asset.id = av.asset_id)
8. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=802,071.42..1,088,354.81 rows=1,481,089 width=783) (actual rows= loops=)

  • Merge Cond: (asset.id = offer_mgt.asset_id)
  • Join Filter: ((offer_mgt.sale_listing_venue_event_id = listing.venue_event_id) AND (offer_mgt.deal_buyerselleraccount_id = deal.buyerselleraccount_id))
9. 0.000 0.000 ↓ 0.0

Gather Merge (cost=802,010.01..1,087,079.33 rows=485,603 width=609) (actual rows= loops=)

  • Workers Planned: 4
10. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=801,009.95..1,028,239.27 rows=121,401 width=609) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=801,009.38..803,187.82 rows=121,401 width=555) (actual rows= loops=)

  • Merge Cond: (asset.id = af.asset_id)
12. 0.000 0.000 ↓ 0.0

Sort (cost=760,788.90..761,092.40 rows=121,401 width=547) (actual rows= loops=)

  • Sort Key: asset.id
13. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=470,068.01..716,388.94 rows=121,401 width=547) (actual rows= loops=)

  • Hash Cond: (asset.asset_status_id = asset_status.id)
14. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=470,066.70..715,972.48 rows=121,401 width=519) (actual rows= loops=)

  • Hash Cond: (seller_account.map_buyerseller_account_type_id = bsa_seller_type.id)
15. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=470,015.31..715,601.59 rows=121,401 width=515) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=470,014.88..656,534.09 rows=121,401 width=472) (actual rows= loops=)

  • Hash Cond: (buyer_account.map_buyerseller_account_type_id = bsa_buyer_type.id)
17. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=469,963.49..656,163.20 rows=121,401 width=468) (actual rows= loops=)

  • Hash Cond: (deal.deal_status_id = deal_status.id)
18. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=469,962.40..655,505.78 rows=121,401 width=440) (actual rows= loops=)

  • Hash Cond: (attendance.badge_type_id = sale_type.id)
19. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=469,961.13..655,073.28 rows=121,401 width=412) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Parallel Hash Left Join (cost=469,960.70..576,359.41 rows=121,401 width=404) (actual rows= loops=)

  • Hash Cond: (deal.buyerselleraccount_id = buyer_account.id)
21. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=420,542.30..513,493.33 rows=121,401 width=353) (actual rows= loops=)

  • Hash Cond: (deal.sold_method_id = sm.id)
22. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=420,541.21..512,835.92 rows=121,401 width=325) (actual rows= loops=)

  • Hash Cond: (listing.venue_event_id = event.id)
23. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=418,999.66..510,975.66 rows=121,401 width=321) (actual rows= loops=)

  • Hash Cond: (lanemap.auction_lane_id = lane.id)
24. 0.000 0.000 ↓ 0.0

Parallel Hash Left Join (cost=418,915.68..510,572.38 rows=121,401 width=323) (actual rows= loops=)

  • Hash Cond: (listing.id = deal.sale_listing_id)
25. 0.000 0.000 ↓ 0.0

Parallel Hash Left Join (cost=282,683.33..363,526.40 rows=121,401 width=294) (actual rows= loops=)

  • Hash Cond: (asset.id = titles.asset_id)
  • Filter: (GREATEST(asset.updated_at, asset.photo_count_updated_at, listing.updated_at, titles.updated_at) >= '1970-01-01 00:00:00+00'::timestamp with time zone)
26. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=222,011.19..301,411.46 rows=364,202 width=252) (actual rows= loops=)

  • Hash Cond: (asset.source_external_system_id = es.id)
27. 0.000 0.000 ↓ 0.0

Parallel Hash Left Join (cost=222,010.06..300,452.90 rows=364,202 width=188) (actual rows= loops=)

  • Hash Cond: (asset.id = listing.asset_id)
28. 0.000 0.000 ↓ 0.0

Parallel Hash Join (cost=137,298.52..214,242.44 rows=364,202 width=144) (actual rows= loops=)

  • Hash Cond: (vehicle.asset_id = asset.id)
29. 0.000 0.000 ↓ 0.0

Parallel Hash Left Join (cost=30,926.91..93,147.57 rows=364,202 width=45) (actual rows= loops=)

  • Hash Cond: (vehicle.id = odo.vehicle_id)
30. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on vehicles vehicle (cost=0.00..50,335.81 rows=361,674 width=49) (actual rows= loops=)

  • Filter: (valid_until IS NULL)
31. 0.000 0.000 ↓ 0.0

Parallel Hash (cost=22,746.45..22,746.45 rows=470,597 width=12) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on vehicle_odometer_readings odo (cost=0.00..22,746.45 rows=470,597 width=12) (actual rows= loops=)

  • Filter: (valid_until IS NULL)
33. 0.000 0.000 ↓ 0.0

Parallel Hash (cost=95,775.50..95,775.50 rows=364,089 width=107) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Merge Join (cost=24,332.31..95,775.50 rows=364,089 width=107) (actual rows= loops=)

  • Merge Cond: (asset.current_custodian_org_id = auction_org.id)
35. 0.000 0.000 ↓ 0.0

Parallel Index Scan using assets__current_custodian_org_id__custodian_stock_number_ix on assets asset (cost=0.43..71,006.10 rows=364,089 width=106) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Index Scan using organizations_pk on organizations auction_org (cost=0.43..48,835.35 rows=1,258,195 width=13) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Parallel Hash (cost=84,045.91..84,045.91 rows=53,250 width=52) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=81.49..84,045.91 rows=53,250 width=52) (actual rows= loops=)

  • Hash Cond: (listing.auction_lane_map_id = lanemap.id)
39. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=1.50..83,825.88 rows=53,250 width=52) (actual rows= loops=)

  • Hash Cond: (listing.sale_listing_status_id = sale_listing_statuses.id)
40. 0.000 0.000 ↓ 0.0

Parallel Index Scan using sale_listings__valid_until_desc_ix on sale_listings listing (cost=0.43..82,755.36 rows=106,500 width=56) (actual rows= loops=)

  • Index Cond: (valid_until IS NULL)
41. 0.000 0.000 ↓ 0.0

Hash (cost=1.05..1.05 rows=2 width=4) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Seq Scan on sale_listing_statuses (cost=0.00..1.05 rows=2 width=4) (actual rows= loops=)

  • Filter: ((code)::text = ANY ('{published,retracted}'::text[]))
43. 0.000 0.000 ↓ 0.0

Hash (cost=52.77..52.77 rows=2,177 width=12) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Seq Scan on auction_lane_mappings lanemap (cost=0.00..52.77 rows=2,177 width=12) (actual rows= loops=)

  • Filter: (valid_until IS NULL)
45. 0.000 0.000 ↓ 0.0

Hash (cost=1.06..1.06 rows=6 width=68) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Seq Scan on external_systems es (cost=0.00..1.06 rows=6 width=68) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Parallel Hash (cost=60,415.36..60,415.36 rows=20,542 width=50) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=1.19..60,415.36 rows=20,542 width=50) (actual rows= loops=)

  • Hash Cond: (titles.certificate_type_id = certification_types.id)
49. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on asset_certifications titles (cost=0.00..58,059.48 rows=287,595 width=54) (actual rows= loops=)

  • Filter: (valid_until IS NULL)
50. 0.000 0.000 ↓ 0.0

Hash (cost=1.18..1.18 rows=1 width=4) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Seq Scan on certification_types (cost=0.00..1.18 rows=1 width=4) (actual rows= loops=)

  • Filter: (satisfies_title = 1)
52. 0.000 0.000 ↓ 0.0

Parallel Hash (cost=134,210.10..134,210.10 rows=94,980 width=45) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.85..134,210.10 rows=94,980 width=45) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Parallel Index Scan using deals__valid_pk_ix on deals deal (cost=0.42..69,125.63 rows=94,980 width=32) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Index Scan using agents_pk on agents (cost=0.43..0.69 rows=1 width=17) (actual rows= loops=)

  • Index Cond: (id = deal.agent_id)
  • Filter: (valid_until IS NULL)
56. 0.000 0.000 ↓ 0.0

Hash (cost=56.77..56.77 rows=2,177 width=10) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Seq Scan on auction_lanes lane (cost=0.00..56.77 rows=2,177 width=10) (actual rows= loops=)

  • Filter: (valid_until IS NULL)
58. 0.000 0.000 ↓ 0.0

Hash (cost=1,035.13..1,035.13 rows=40,513 width=12) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Seq Scan on venue_events event (cost=0.00..1,035.13 rows=40,513 width=12) (actual rows= loops=)

  • Filter: (valid_until IS NULL)
60. 0.000 0.000 ↓ 0.0

Hash (cost=1.04..1.04 rows=4 width=36) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Seq Scan on sale_methods sm (cost=0.00..1.04 rows=4 width=36) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Parallel Hash (cost=45,436.18..45,436.18 rows=178,818 width=51) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on buyerselleraccounts buyer_account (cost=0.00..45,436.18 rows=178,818 width=51) (actual rows= loops=)

  • Filter: (valid_until IS NULL)
64. 0.000 0.000 ↓ 0.0

Index Scan using venue_event_attendance__venue_event_id_agent_id_valid_until_ux on venue_event_attendance attendance (cost=0.43..0.64 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((venue_event_id = listing.venue_event_id) AND (agent_id = deal.agent_id) AND (valid_until IS NULL))
65. 0.000 0.000 ↓ 0.0

Hash (cost=1.12..1.12 rows=12 width=36) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Seq Scan on badge_types sale_type (cost=0.00..1.12 rows=12 width=36) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Hash (cost=1.04..1.04 rows=4 width=36) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Seq Scan on deal_statuses deal_status (cost=0.00..1.04 rows=4 width=36) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Hash (cost=31.73..31.73 rows=1,573 width=12) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Seq Scan on map_buyerselleraccount_types bsa_buyer_type (cost=0.00..31.73 rows=1,573 width=12) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Index Scan using buyerselleraccounts__buyerseller_org_id_ix on buyerselleraccounts seller_account (cost=0.42..0.48 rows=1 width=59) (actual rows= loops=)

  • Index Cond: (buyerseller_org_id = asset.owner_org_id)
  • Filter: (valid_until IS NULL)
72. 0.000 0.000 ↓ 0.0

Hash (cost=31.73..31.73 rows=1,573 width=12) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Seq Scan on map_buyerselleraccount_types bsa_seller_type (cost=0.00..31.73 rows=1,573 width=12) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Hash (cost=1.14..1.14 rows=14 width=36) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Seq Scan on asset_statuses asset_status (cost=0.00..1.14 rows=14 width=36) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Materialize (cost=40,220.49..41,174.85 rows=190,872 width=16) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

Sort (cost=40,220.49..40,697.67 rows=190,872 width=16) (actual rows= loops=)

  • Sort Key: af.asset_id
78. 0.000 0.000 ↓ 0.0

Index Scan using asset_files__primary_valid_visible_asset_id_ix on asset_files af (cost=0.43..21,614.87 rows=190,872 width=16) (actual rows= loops=)

  • Index Cond: ((is_primary = 1) AND (valid_until IS NULL))
79. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..1.84 rows=1 width=70) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

Index Scan using files__pk on files fi (cost=0.43..1.67 rows=1 width=74) (actual rows= loops=)

  • Index Cond: (af.file_id = id)
  • Filter: (valid_until IS NULL)
81. 0.000 0.000 ↓ 0.0

Index Scan using file_types_pk on file_types (cost=0.13..0.15 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = fi.file_type_id)
  • Filter: (is_image = 1)
82. 0.000 0.000 ↓ 0.0

Sort (cost=61.41..61.43 rows=6 width=214) (actual rows= loops=)

  • Sort Key: offer_mgt.asset_id
83. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=42.89..61.34 rows=6 width=214) (actual rows= loops=)

  • Hash Cond: (offer_events.asset_snapshot_id = offer_mgt.id)
84. 0.000 0.000 ↓ 0.0

Seq Scan on offer_negotiation_events offer_events (cost=0.00..16.10 rows=610 width=36) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Hash (cost=42.86..42.86 rows=2 width=190) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=26.66..42.86 rows=2 width=190) (actual rows= loops=)

87. 0.000 0.000 ↓ 0.0

Subquery Scan on offer_mgt (cost=26.52..38.52 rows=2 width=192) (actual rows= loops=)

  • Filter: (offer_mgt.rnum = 1)
88. 0.000 0.000 ↓ 0.0

WindowAgg (cost=26.52..34.52 rows=320 width=228) (actual rows= loops=)

89. 0.000 0.000 ↓ 0.0

Sort (cost=26.52..27.32 rows=320 width=200) (actual rows= loops=)

  • Sort Key: snaps.asset_id, snaps.sale_listing_venue_event_id, snaps.deal_buyerselleraccount_id, snaps.created_at DESC
90. 0.000 0.000 ↓ 0.0

Seq Scan on asset_snapshots snaps (cost=0.00..13.20 rows=320 width=200) (actual rows= loops=)

91. 0.000 0.000 ↓ 0.0

Index Scan using announcement_lights_pk on announcement_lights deal_lights (cost=0.14..2.16 rows=1 width=6) (actual rows= loops=)

  • Index Cond: (id = offer_mgt.sale_listing_announcement_lights_id)
92. 0.000 0.000 ↓ 0.0

Finalize GroupAggregate (cost=55,798.74..81,724.07 rows=179,901 width=32) (actual rows= loops=)

  • Group Key: av.asset_id
93. 0.000 0.000 ↓ 0.0

Gather Merge (cost=55,798.74..78,575.81 rows=179,900 width=16) (actual rows= loops=)

  • Workers Planned: 4
94. 0.000 0.000 ↓ 0.0

Partial GroupAggregate (cost=54,798.68..56,147.93 rows=44,975 width=16) (actual rows= loops=)

  • Group Key: av.asset_id
95. 0.000 0.000 ↓ 0.0

Sort (cost=54,798.68..54,911.12 rows=44,975 width=76) (actual rows= loops=)

  • Sort Key: av.asset_id
96. 0.000 0.000 ↓ 0.0

Hash Join (cost=2.21..50,180.82 rows=44,975 width=76) (actual rows= loops=)

  • Hash Cond: (av.valuation_source_id = avs.id)
97. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.10..49,907.05 rows=56,219 width=52) (actual rows= loops=)

  • Hash Cond: ((av.valuation_source_id = avt.valuation_source_id) AND (av.valuation_type_id = avt.id))
98. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on asset_valuations av (cost=0.00..48,553.18 rows=224,876 width=20) (actual rows= loops=)

  • Filter: (valid_until IS NULL)
99. 0.000 0.000 ↓ 0.0

Hash (cost=1.04..1.04 rows=4 width=40) (actual rows= loops=)

100. 0.000 0.000 ↓ 0.0

Seq Scan on asset_valuation_types avt (cost=0.00..1.04 rows=4 width=40) (actual rows= loops=)

101. 0.000 0.000 ↓ 0.0

Hash (cost=1.05..1.05 rows=5 width=36) (actual rows= loops=)

102. 0.000 0.000 ↓ 0.0

Seq Scan on asset_valuation_sources avs (cost=0.00..1.05 rows=5 width=36) (actual rows= loops=)

103. 0.000 0.000 ↓ 0.0

Hash (cost=3,651.51..3,651.51 rows=178,251 width=16) (actual rows= loops=)

104. 0.000 0.000 ↓ 0.0

Seq Scan on map_vehicle_models models_map (cost=0.00..3,651.51 rows=178,251 width=16) (actual rows= loops=)

105. 0.000 0.000 ↓ 0.0

Hash (cost=169.21..169.21 rows=8,621 width=14) (actual rows= loops=)

106. 0.000 0.000 ↓ 0.0

Seq Scan on map_vehicle_makes makes_map (cost=0.00..169.21 rows=8,621 width=14) (actual rows= loops=)

107. 0.000 0.000 ↓ 0.0

Hash (cost=2.92..2.92 rows=92 width=12) (actual rows= loops=)

108. 0.000 0.000 ↓ 0.0

Seq Scan on vehicle_makes makes (cost=0.00..2.92 rows=92 width=12) (actual rows= loops=)

109. 0.000 0.000 ↓ 0.0

Hash (cost=40.61..40.61 rows=1,961 width=14) (actual rows= loops=)

110. 0.000 0.000 ↓ 0.0

Seq Scan on map_exterior_colors ec_map (cost=0.00..40.61 rows=1,961 width=14) (actual rows= loops=)

111. 0.000 0.000 ↓ 0.0

Hash (cost=1.21..1.21 rows=21 width=36) (actual rows= loops=)

112. 0.000 0.000 ↓ 0.0

Seq Scan on exterior_colors ec (cost=0.00..1.21 rows=21 width=36) (actual rows= loops=)

113.          

SubPlan (for Hash Left Join)

114. 0.000 0.000 ↓ 0.0

Result (cost=5.32..5.34 rows=1 width=8) (actual rows= loops=)

115.          

Initplan (for Result)

116. 0.000 0.000 ↓ 0.0

Aggregate (cost=5.31..5.32 rows=1 width=8) (actual rows= loops=)

117. 0.000 0.000 ↓ 0.0

Index Scan using nos_records__asset_id_idx on nos_records (cost=0.42..5.30 rows=4 width=0) (actual rows= loops=)

  • Index Cond: (asset_id = asset.id)
  • Filter: (has_run = 1)