Re: slow query with inline function on AWS RDS with RDS 24x large

BRIN index is only on the date_time column, I even tried with btree index
with no performance gains.

On Fri, Jun 4, 2021 at 11:23 AM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> pá 4. 6. 2021 v 10:07 odesílatel Ayub Khan <ayub(dot)hp(at)gmail(dot)com> napsal:
>
>>
>> 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
>>
>>
> -> 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))
>
> Looks so the BRIN index is not in good condition. Maybe you need reindex,
> maybe BRIN index is not good format for your data.
>
> There are lot of data - few millions of rows
>
> Regards
>
> Pavel
>
>