sql - 为报告目的减少查询运行时间

Question

以下查询大约需要 58 分钟才能运行，我想对其进行优化以提高效率。weight_events 表目前有大约 150 万行。

尝试在表上添加索引，但它不会更改查询计划。

explain select round(sum(w1.current_sum_volume)::numeric / 1000, 3) as total
from bulk_lots b
join list_items bltype on bltype.id = b.list_item_id
join weight_events w1 on (
  w1.owner_type = 'BulkLot'
  and w1.owner_id = b.id
  and w1.created_at < '01-01-2019')
left join weight_events w2 on (
  w2.owner_type = 'BulkLot'
  and w2.owner_id = b.id
  and w2.created_at < '01-31-2019'
  and w2.created_at > w1.created_at)
where w2.id is null
  and bltype.name = 'Oil'

表上的索引

  add_index "weight_events", ["bulk_lot_id"], name: "index_weight_events_on_bulk_lot_id", using: :btree
  add_index "weight_events", ["destroyed_id", "destroyed_type"], name: "index_weight_events_on_destroyed_id_and_destroyed_type", using: :btree
  add_index "weight_events", ["next_id"], name: "index_weight_events_on_next_id", using: :btree
  add_index "weight_events", ["owner_type", "owner_id"], name: "index_weight_events_on_owner_type_and_owner_id", using: :btree
  add_index "weight_events", ["previous_id"], name: "index_weight_events_on_previous_id", using: :btree
  add_index "weight_events", ["undoes_id"], name: "index_weight_events_on_undoes_id", using: :btree

预计查询时间会比 58 分钟快得多。

标签： sqlpostgresql