HashAggregate (cost=334063.59..334064.17 rows=58 width=213) (actual time=1180.016..1180.053 rows=49 loops=1) Group Key: c.city_id, '2022-02-25 23:09:26.587835'::timestamp without time zone, (count(*)) -> Nested Loop Left Join (cost=154967.02..312398.93 rows=133321 width=92) (actual time=450.918..961.314 rows=156105 loops=1) Join Filter: (c_1.city_id = c.city_id) Rows Removed by Join Filter: 103951 -> Hash Join (cost=2343.27..154695.90 rows=133321 width=76) (actual time=12.345..456.445 rows=156105 loops=1) Hash Cond: (b.city_id = c.city_id) -> Hash Join (cost=2324.74..154301.83 rows=133321 width=55) (actual time=12.266..420.340 rows=156105 loops=1) Hash Cond: (o.branch_id = b.branch_id) -> Append (cost=0.57..151627.65 rows=133321 width=55) (actual time=0.022..347.441 rows=156105 loops=1) Subplans Removed: 23 -> Index Scan using restaurant_order_p_202202_202203_date_time_idx on restaurant_order_p_202202_202203 o (cost=0.56..150904.37 rows=133298 width=55) (actual time=0.022..333.768 rows=156105 loops=1) Index Cond: ((date_time >= '2022-02-25 05:00:00'::timestamp without time zone) AND (date_time <= '2022-02-25 23:09:26.587835'::timestamp without time zone)) -> Hash (cost=1942.41..1942.41 rows=30541 width=16) (actual time=12.172..12.172 rows=29242 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 1627kB -> Seq Scan on branch b (cost=0.00..1942.41 rows=30541 width=16) (actual time=0.005..7.358 rows=29242 loops=1) -> Hash (cost=17.80..17.80 rows=58 width=29) (actual time=0.072..0.072 rows=58 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 12kB -> Index Only Scan using city_idx$$_274b0022 on city c (cost=0.27..17.80 rows=58 width=29) (actual time=0.011..0.056 rows=58 loops=1) Index Cond: (city_id IS NOT NULL) Heap Fetches: 40 -> Materialize (cost=152623.75..155703.21 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=156105) -> GroupAggregate (cost=152623.75..155703.19 rows=1 width=16) (actual time=438.567..438.611 rows=2 loops=1) Group Key: c_1.city_id -> Nested Loop Left Join (cost=152623.75..155703.18 rows=1 width=8) (actual time=434.578..438.605 rows=6 loops=1) -> Nested Loop (cost=152623.32..155701.62 rows=1 width=16) (actual time=434.569..438.581 rows=6 loops=1) Join Filter: (b_1.city_id = c_1.city_id) Rows Removed by Join Filter: 342 -> Index Only Scan using city_pk on city c_1 (cost=0.27..13.27 rows=58 width=8) (actual time=0.015..0.062 rows=58 loops=1) Heap Fetches: 40 -> Materialize (cost=152623.06..155687.49 rows=1 width=16) (actual time=7.492..7.560 rows=6 loops=58) -> Nested Loop (cost=152623.06..155687.48 rows=1 width=16) (actual time=434.549..438.450 rows=6 loops=1) -> Nested Loop (cost=152622.77..155687.17 rows=1 width=32) (actual time=434.539..438.425 rows=6 loops=1) Join Filter: (r_1.restaurant_id = b_1.restaurant_id) -> Nested Loop (cost=152622.48..155685.14 rows=1 width=24) (actual time=434.529..438.396 rows=6 loops=1) -> GroupAggregate (cost=152621.92..152628.01 rows=79 width=55) (actual time=434.465..438.214 rows=3 loops=1) Group Key: (round(ro_1.total_amount, 2)), ro_1.phone, r_1.restaurant_id Filter: (count(1) > 1) Rows Removed by Filter: 9365 -> Sort (cost=152621.92..152622.51 rows=236 width=55) (actual time=434.273..435.044 rows=9371 loops=1) Sort Key: (round(ro_1.total_amount, 2)), ro_1.phone, r_1.restaurant_id Sort Method: quicksort Memory: 1117kB -> Nested Loop Left Join (cost=1.83..152612.62 rows=236 width=55) (actual time=163.508..417.596 rows=9371 loops=1) -> Nested Loop (cost=1.41..152245.05 rows=236 width=37) (actual time=163.493..396.303 rows=9371 loops=1) -> Nested Loop (cost=1.14..152178.31 rows=236 width=45) (actual time=163.475..377.443 rows=9371 loops=1) -> Nested Loop (cost=0.85..152105.61 rows=236 width=45) (actual time=163.459..353.730 rows=9371 loops=1) -> Append (cost=0.57..151628.83 rows=236 width=37) (actual time=163.439..322.719 rows=9371 loops=1) Subplans Removed: 23 -> Index Scan using restaurant_order_p_202202_202203_date_time_idx on restaurant_order_p_202202_202203 ro_1 (cost=0.56..151570.86 rows=213 width=34) (actual time=163.438..321.864 rows=9371 loops=1) Index Cond: ((date_time >= '2022-02-25 05:00:00'::timestamp without time zone) AND (date_time <= '2022-02-25 23:09:26.587835'::timestamp without time zone)) Filter: ((schedule_datetime IS NULL) AND (order_status_code <> ALL ('{T,J,C,D}'::bpchar[]))) Rows Removed by Filter: 146734 -> Index Scan using branch_idx$$_274b0038 on branch b_2 (cost=0.29..2.02 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=9371) Index Cond: (branch_id = ro_1.branch_id) -> Index Only Scan using restaurant_idx$$_274b003d on restaurant r_1 (cost=0.29..0.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=9371) Index Cond: (restaurant_id = b_2.restaurant_id) Heap Fetches: 6155 -> Index Only Scan using city_pk on city c_2 (cost=0.27..0.28 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=9371) Index Cond: (city_id = b_2.city_id) Heap Fetches: 9141 -> Index Only Scan using order_offer_map_order_id on order_offer_map oom_1 (cost=0.43..1.55 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=9371) Index Cond: (order_id = ro_1.order_id) Heap Fetches: 34 -> Append (cost=0.57..38.45 rows=24 width=37) (actual time=0.039..0.059 rows=2 loops=3) Subplans Removed: 23 -> Index Scan using restaurant_order_p_202202_202203_phone_idx on restaurant_order_p_202202_202203 ro (cost=0.56..14.11 rows=1 width=34) (actual time=0.038..0.057 rows=2 loops=3) Index Cond: ((phone)::text = (ro_1.phone)::text) Filter: ((schedule_datetime IS NULL) AND (date_time >= '2022-02-25 05:00:00'::timestamp without time zone) AND (date_time <= '2022-02-25 23:09:26.587835'::timestamp without time zone) AND (order_status_code <> ALL ('{T,J,C,D}'::bpchar[])) AND ((round(ro_1.total_amount, 2)) = round(total_amount, 2))) Rows Removed by Filter: 12 -> Index Scan using branch_idx$$_274b0038 on branch b_1 (cost=0.29..2.02 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=6) Index Cond: (branch_id = ro.branch_id) -> Index Only Scan using restaurant_idx$$_274b003d on restaurant r (cost=0.29..0.31 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=6) Index Cond: (restaurant_id = b_1.restaurant_id) Heap Fetches: 6 -> Index Only Scan using order_offer_map_order_id on order_offer_map oom (cost=0.43..1.55 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=6) Index Cond: (order_id = ro.order_id) Heap Fetches: 0 Planning Time: 27.392 ms Execution Time: 1180.579 ms