below query is slow even with no data
explain ANALYZE
WITH business AS( SELECT * FROM get_businessday_utc_f() start_date)
SELECT ro.order_id,
ro.date_time,
round(ro.order_amount, 2) AS order_amount,
b.branch_id,
b.branch_name,
st_x(b.location) AS from_x,
st_y(b.location) AS from_y,
b.user_id AS branch_user_id,
b.contact_info,
r.restaurant_id,
c.city_id,
c.city_name,
c.city_name_ar,
st_linefromtext(((((((('LINESTRING('::text || st_x(b.location)) ||
' '::text) || st_y(b.location)) || ','::text) ||
st_x(ro.location_geometry)) || ' '::text) ||
st_y(ro.location_geometry)) || ')'::text, 28355) AS from_to,
to_char(ro.date_time, 'HH24:MI'::text) AS order_time,
ro.customer_comment,
'N'::text AS is_new_customer,
ro.picked_up_time,
ro.driver_assigned_date_time,
oom.offer_amount,
oom.offer_type_code AS offer_type,
ro.uk_vat
FROM business, restaurant_order ro
JOIN branch b ON b.branch_id = ro.branch_id
JOIN restaurant r ON r.restaurant_id = b.restaurant_id
JOIN city c ON c.city_id = b.city_id
LEFT JOIN order_offer_map oom using (order_id)
WHERE ro.date_time >= business.start_date AND ro.date_time<=
f_now_immutable_with_tz();
Hash Left Join (cost=55497.32..5417639.59 rows=5397276 width=291)
(actual time=1056.926..1056.934 rows=0 loops=1)
Hash Cond: (ro.order_id = oom.order_id)
-> Hash Join (cost=6584.61..3674143.44 rows=5397276 width=209)
(actual time=1056.926..1056.932 rows=0 loops=1)
Hash Cond: (ro.branch_id = b.branch_id)
-> Nested Loop (cost=5427.94..3546726.47 rows=19275986
width=108) (actual time=1036.809..1036.810 rows=0 loops=1)
-> Function Scan on start_date (cost=0.00..0.01 rows=1
width=8) (actual time=0.006..0.008 rows=1 loops=1)
-> Bitmap Heap Scan on restaurant_order ro
(cost=5427.94..3353966.60 rows=19275986 width=108) (actual
time=1036.793..1036.793 rows=0 loops=1)
Recheck Cond: ((date_time >=
start_date.start_date) AND (date_time <= '2021-06-04
08:05:32.784199+00'::timestamp with time zone))
Rows Removed by Index Recheck: 5039976
Heap Blocks: lossy=275230
-> Bitmap Index Scan on rest_ord_date_brin
(cost=0.00..608.94 rows=19359111 width=0) (actual time=14.037..14.038
rows=2917120 loops=1)
Index Cond: ((date_time >=
start_date.start_date) AND (date_time <= '2021-06-04
08:05:32.784199+00'::timestamp with time zone))
-> Hash (cost=1083.35..1083.35 rows=5866 width=109) (actual
time=20.106..20.109 rows=20949 loops=1)
Buckets: 32768 (originally 8192) Batches: 1 (originally
1) Memory Usage: 3112kB
-> Hash Join (cost=343.29..1083.35 rows=5866
width=109) (actual time=1.620..14.539 rows=20949 loops=1)
Hash Cond: (b.restaurant_id = r.restaurant_id)
-> Hash Join (cost=2.26..726.91 rows=5866
width=109) (actual time=0.029..8.597 rows=20949 loops=1)
Hash Cond: (b.city_id = c.city_id)
-> Seq Scan on branch b (cost=0.00..668.49
rows=20949 width=88) (actual time=0.004..1.609 rows=20949 loops=1)
-> Hash (cost=1.56..1.56 rows=56 width=29)
(actual time=0.020..0.021 rows=56 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Seq Scan on city c
(cost=0.00..1.56 rows=56 width=29) (actual time=0.004..0.010 rows=56
loops=1)
-> Hash (cost=233.42..233.42 rows=8609 width=8)
(actual time=1.575..1.575 rows=8609 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 465kB
-> Index Only Scan using
"restaurant_idx$$_274b003d" on restaurant r (cost=0.29..233.42
rows=8609 width=8) (actual time=0.006..0.684 rows=8609 loops=1)
Heap Fetches: 0
-> Hash (cost=33000.09..33000.09 rows=1273009 width=13) (never executed)
-> Seq Scan on order_offer_map oom (cost=0.00..33000.09
rows=1273009 width=13) (never executed)
Planning Time: 1.180 ms
Execution Time: 1057.535 ms
could some one explain why it is slow, if I insert 50k records the
execution time reaches 20 seconds
