我正在try Select 属于具有多个订单的行程的订单.
我try 了许多方法,但找不到如何获得性能良好的查询.
重现这里的问题的是设置(这里是100000行,但实际上要在db-fiddle上看到超时的行数超过1000000行).
Schema (PostgreSQL v14)个
create table trips (id bigint primary key);
create table orders (id bigint primary key, trip_id bigint);
create index trips_idx on trips (id);
create index orders_idx on orders (id);
create index orders_trip_idx on orders (trip_id);
insert into trips (id) select seq from generate_series(1,100000) seq;
insert into orders (id, trip_id) select seq, floor(random() * 100000 + 1) from generate_series(1,100000) seq;
Query #1个
explain analyze select orders.id
from orders
inner join trips on trips.id = orders.trip_id
inner join orders trips_orders on trips_orders.trip_id = trips.id
group by orders.id, trips.id
having count(trips_orders) > 1
limit 50
;
以下是pgimerard在真正的问题上给我的答案: