slow query
I checked all the indexes are defined on the tables however the query seems
slow, below is the plan. Can any one give any pointers to verify ?
SELECT a.menu_item_id, a.menu_item_name, a.menu_item_category_id,
b.menu_item_category_desc, c.menu_item_variant_id,
c.menu_item_variant_type_id, c.price, c.size_id,
c.parent_menu_item_variant_id, d.menu_item_variant_type_desc,
e.size_desc, f.currency_code, a.image, a.mark_id, m.mark_name
FROM menu_item_category AS b, menu_item_variant AS c,
menu_item_variant_type AS d, item_size AS e, restaurant AS f,
menu_item AS a
LEFT OUTER JOIN mark AS m ON (a.mark_id = m.mark_id) WHERE
a.menu_item_category_id = b.menu_item_category_id AND a.menu_item_id =
c.menu_item_id AND c.menu_item_variant_type_id =
d.menu_item_variant_type_id AND d.is_hidden = 'false' AND c.size_id =
e.size_id AND a.restaurant_id = f.restaurant_id AND f.restaurant_id =
1528 AND (a.menu_item_category_id = NULL OR NULL IS NULL)
AND c.menu_item_variant_id = (SELECT min(menu_item_variant_id) FROM
menu_item_variant WHERE menu_item_id = a.menu_item_id AND deleted =
'N' limit 1) AND a.active = 'Y'
AND (CONCAT_WS('', ',', a.hidden_branch_ids, ',') NOT LIKE
CONCAT_WS('', '%,4191,%') OR NULL IS NULL)
AND is_menu_item_available(a.menu_item_id, 'Y') = 'Y'
ORDER BY a.row_order, menu_item_id;
below is the plan
Sort (cost=189.27..189.27 rows=1 width=152) (actual time=5.876..5.885
rows=89 loops=1)
" Sort Key: a.row_order, a.menu_item_id"
Sort Method: quicksort Memory: 48kB
-> Nested Loop Left Join (cost=5.19..189.26 rows=1 width=152)
(actual time=0.188..5.809 rows=89 loops=1)
Join Filter: (a.mark_id = m.mark_id)
Rows Removed by Join Filter: 267
-> Nested Loop (cost=5.19..188.19 rows=1 width=148) (actual
time=0.181..5.629 rows=89 loops=1)
-> Nested Loop (cost=4.90..185.88 rows=1 width=152)
(actual time=0.174..5.443 rows=89 loops=1)
-> Nested Loop (cost=4.61..185.57 rows=1
width=144) (actual time=0.168..5.272 rows=89 loops=1)
-> Nested Loop (cost=4.32..185.25 rows=1
width=136) (actual time=0.162..5.066 rows=89 loops=1)
-> Nested Loop (cost=0.71..179.62
rows=1 width=99) (actual time=0.137..3.986 rows=89 loops=1)
-> Index Scan using
menu_item_restaurant_id on menu_item a (cost=0.42..177.31 rows=1
width=87) (actual time=0.130..3.769 rows=89 loops=1)
Index Cond: (restaurant_id = 1528)
"Filter: ((active =
'Y'::bpchar) AND (is_menu_item_available(menu_item_id, 'Y'::bpchar) =
'Y'::bpchar))"
Rows Removed by Filter: 194
-> Index Scan using
menu_item_category_pk on menu_item_category b (cost=0.29..2.31 rows=1
width=20) (actual time=0.002..0.002 rows=1 loops=89)
Index Cond:
(menu_item_category_id = a.menu_item_category_id)
-> Index Scan using
menu_item_variant_pk on menu_item_variant c (cost=3.60..5.62 rows=1
width=45) (actual time=0.002..0.002 rows=1 loops=89)
Index Cond:
(menu_item_variant_id = (SubPlan 1))
Filter: (a.menu_item_id = menu_item_id)
SubPlan 1
-> Limit (cost=3.17..3.18
rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=89)
-> Aggregate
(cost=3.17..3.18 rows=1 width=8) (actual time=0.008..0.008 rows=1
loops=89)
-> Index Scan
using "idx$$_023a0001" on menu_item_variant (cost=0.43..3.15 rows=8
width=8) (actual time=0.004..0.007 rows=7 loops=89)
Index Cond:
(menu_item_id = a.menu_item_id)
Filter:
(deleted = 'N'::bpchar)
Rows Removed
by Filter: 4
-> Index Scan using
menu_item_variant_type_pk on menu_item_variant_type d
(cost=0.29..0.31 rows=1 width=16) (actual time=0.002..0.002 rows=1
loops=89)
Index Cond: (menu_item_variant_type_id
= c.menu_item_variant_type_id)
Filter: ((is_hidden)::text = 'false'::text)
-> Index Scan using size_pk on item_size e
(cost=0.29..0.31 rows=1 width=16) (actual time=0.001..0.001 rows=1
loops=89)
Index Cond: (size_id = c.size_id)
-> Index Scan using "restaurant_idx$$_274b003d" on
restaurant f (cost=0.29..2.30 rows=1 width=12) (actual
time=0.001..0.002 rows=1 loops=89)
Index Cond: (restaurant_id = 1528)
-> Seq Scan on mark m (cost=0.00..1.03 rows=3 width=12)
Re: slow query
> On Jun 8, 2021, at 09:03, Ayub Khan wrote: > I checked all the indexes are defined on the tables however the query seems > slow, below is the plan. It's currently running in slightly under six milliseconds. That seems reasonably fast given the number of operations required to fulfill it.
Re: slow query
In AWS RDS performance insights the client writes is high and the api
which receives data on the mobile side is slow during load test.
On Tue, 8 Jun 2021, 19:03 Ayub Khan, wrote:
>
> I checked all the indexes are defined on the tables however the query
> seems slow, below is the plan. Can any one give any pointers to verify ?
>
> SELECT a.menu_item_id, a.menu_item_name, a.menu_item_category_id,
> b.menu_item_category_desc, c.menu_item_variant_id,
> c.menu_item_variant_type_id, c.price, c.size_id,
> c.parent_menu_item_variant_id, d.menu_item_variant_type_desc, e.size_desc,
> f.currency_code, a.image, a.mark_id, m.mark_name
>
> FROM menu_item_category AS b, menu_item_variant AS c, menu_item_variant_type
> AS d, item_size AS e, restaurant AS f, menu_item AS a
>
> LEFT OUTER JOIN mark AS m ON (a.mark_id = m.mark_id) WHERE
> a.menu_item_category_id = b.menu_item_category_id AND a.menu_item_id =
> c.menu_item_id AND c.menu_item_variant_type_id = d.menu_item_variant_type_id
> AND d.is_hidden = 'false' AND c.size_id = e.size_id AND a.restaurant_id =
> f.restaurant_id AND f.restaurant_id = 1528 AND (a.menu_item_category_id =
> NULL OR NULL IS NULL)
>
> AND c.menu_item_variant_id = (SELECT min(menu_item_variant_id) FROM
> menu_item_variant WHERE menu_item_id = a.menu_item_id AND deleted = 'N' limit
> 1) AND a.active = 'Y'
> AND (CONCAT_WS('', ',', a.hidden_branch_ids, ',') NOT LIKE CONCAT_WS('',
> '%,4191,%') OR NULL IS NULL)
> AND is_menu_item_available(a.menu_item_id, 'Y') = 'Y'
>
> ORDER BY a.row_order, menu_item_id;
>
>
> below is the plan
>
>
> Sort (cost=189.27..189.27 rows=1 width=152) (actual time=5.876..5.885
> rows=89 loops=1)
> " Sort Key: a.row_order, a.menu_item_id"
> Sort Method: quicksort Memory: 48kB
> -> Nested Loop Left Join (cost=5.19..189.26 rows=1 width=152) (actual
> time=0.188..5.809 rows=89 loops=1)
> Join Filter: (a.mark_id = m.mark_id)
> Rows Removed by Join Filter: 267
> -> Nested Loop (cost=5.19..188.19 rows=1 width=148) (actual
> time=0.181..5.629 rows=89 loops=1)
> -> Nested Loop (cost=4.90..185.88 rows=1 width=152) (actual
> time=0.174..5.443 rows=89 loops=1)
> -> Nested Loop (cost=4.61..185.57 rows=1 width=144)
> (actual time=0.168..5.272 rows=89 loops=1)
> -> Nested Loop (cost=4.32..185.25 rows=1
> width=136) (actual time=0.162..5.066 rows=89 loops=1)
> -> Nested Loop (cost=0.71..179.62 rows=1
> width=99) (actual time=0.137..3.986 rows=89 loops=1)
> -> Index Scan using
> menu_item_restaurant_id on menu_item a (cost=0.42..177.31 rows=1 width=87)
> (actual time=0.130..3.769 rows=89 loops=1)
> Index Cond: (restaurant_id = 1528)
> "Filter: ((active = 'Y'::bpchar)
> AND (is_menu_item_available(menu_item_id, 'Y'::bpchar) = 'Y'::bpchar))"
> Rows Removed by Filter: 194
> -> Index Scan using
> menu_item_category_pk on menu_item_category b (cost=0.29..2.31 rows=1
> width=20) (actual time=0.002..0.002 rows=1 loops=89)
> Index Cond:
> (menu_item_category_id = a.menu_item_category_id)
> -> Index Scan using menu_item_variant_pk on
> menu_item_variant c (cost=3.60..5.62 rows=1 width=45) (actual
> time=0.002..0.002 rows=1 loops=89)
> Index Cond: (menu_item_variant_id =
> (SubPlan 1))
> Filter: (a.menu_item_id = menu_item_id)
> SubPlan 1
> -> Limit (cost=3.17..3.18 rows=1
> width=8) (actual time=0.009..0.009 rows=1 loops=89)
> -> Aggregate (cost=3.17..3.18
> rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=89)
> -> Index Scan using
> "idx$$_023a0001" on menu_item_variant (cost=0.43..3.15 rows=8 width=8)
> (actual time=0.004..0.007 rows=7 loops=89)
> Index Cond:
> (menu_item_id = a.menu_item_id)
> Filter: (deleted =
> 'N'::bpchar)
> Rows Removed by
> Filter: 4
> -> Index Scan using menu_item_variant_type_pk on
> menu_item_variant_type d (cost=0.29..0.31 rows=1 width=16) (actual
> time=0.002..0.002 rows=1 loops=89)
> Index Cond: (menu_item_variant_type_id =
> c.menu_item_variant_type_id)
> Filter: ((is_hidden)::text = 'false'::text)
> -> Index Scan using size_pk on item_size e
> (cost=0.29..0.31
Re: slow query
Ayub Khan writes: > I checked all the indexes are defined on the tables however the query seems > slow, below is the plan. Can any one give any pointers to verify ? You might try to do something about the poor selectivity estimate here: > -> Index Scan using > menu_item_restaurant_id on menu_item a (cost=0.42..177.31 rows=1 > width=87) (actual time=0.130..3.769 rows=89 loops=1) > Index Cond: (restaurant_id = 1528) > "Filter: ((active = > 'Y'::bpchar) AND (is_menu_item_available(menu_item_id, 'Y'::bpchar) = > 'Y'::bpchar))" > Rows Removed by Filter: 194 If the planner realized that this'd produce O(100) rows not 1, it'd likely have picked a different plan. I'm guessing that the issue is lack of knowledge about what is_menu_item_available() will do. Maybe you could replace that with a status column? regards, tom lane
Re: slow query
below is function definition of is_menu_item_available, for each item
based on current day time it returns when it's available or not. The same
api works fine on oracle, I am seeing this slowness after migrating the
queries to postgresql RDS on AWS
CREATE OR REPLACE FUNCTION is_menu_item_available(
i_menu_item_id bigint,
i_check_availability character)
RETURNS character
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
l_current_day NUMERIC(1);
o_time CHARACTER VARYING(10);
l_current_interval INTERVAL DAY TO SECOND(2);
item_available_count NUMERIC(10);
BEGIN
item_available_count := 0;
BEGIN
IF i_check_availability = 'Y' THEN
BEGIN
SELECT
CASE TO_CHAR(now(), 'fmday')
WHEN 'monday' THEN 1
WHEN 'tuesday' THEN 2
WHEN 'wednesday' THEN 3
WHEN 'thursday' THEN 4
WHEN 'friday' THEN 5
WHEN 'saturday' THEN 6
WHEN 'sunday' THEN 7
END AS d
INTO STRICT l_current_day;
select (('0 ' ||
EXTRACT (HOUR FROM ((now() at time zone 'UTC') at time zone '+03:00'))
|| ':' ||
EXTRACT (minute FROM ((now() at time zone 'UTC') at time zone
'+03:00')) || ':00') :: interval)
INTO l_current_interval;
END;
BEGIN
SELECT
COUNT(*)
INTO STRICT item_available_count
FROM menu_item_availability
WHERE menu_item_id = i_menu_item_id;
IF item_available_count = 0 THEN
RETURN 'Y';
ELSE
SELECT
COUNT(*)
INTO STRICT item_available_count
FROM menu_item_availability AS mia, availability AS av
WHERE mia.menu_item_id = i_menu_item_id
AND mia.availability_id = av.id
AND date_trunc('DAY',now()) + l_current_interval >= (CASE
WHEN l_current_interval < '6 hour'::INTERVAL THEN
date_trunc('DAY',now()) + av.start_time - (1::NUMERIC || ' days')::INTERVAL
WHEN l_current_interval >= '6 hour'::INTERVAL THEN
date_trunc('DAY',now())+ av.start_time
END) AND date_trunc('DAY',now()) + l_current_interval <= (CASE
WHEN l_current_interval < '6 hour'::INTERVAL THEN
date_trunc('DAY',now()) + av.end_time - (1::NUMERIC || ' days')::INTERVAL
WHEN l_current_interval >= '6 hour'::INTERVAL THEN
date_trunc('DAY',now()) + av.end_time
END) AND (av.day_of_week LIKE CONCAT_WS('', '%', l_current_day, '%')
OR av.day_of_week LIKE '%0%') AND is_deleted = 0;
END IF;
END;
BEGIN
IF item_available_count > 0 THEN
RETURN 'Y';
ELSE
RETURN 'N';
END IF;
END;
ELSE
RETURN 'Y';
END IF;
END;
END;
$BODY$;
On Tue, Jun 8, 2021 at 7:03 PM Ayub Khan wrote:
>
> I checked all the indexes are defined on the tables however the query
> seems slow, below is the plan. Can any one give any pointers to verify ?
>
> SELECT a.menu_item_id, a.menu_item_name, a.menu_item_category_id,
> b.menu_item_category_desc, c.menu_item_variant_id,
> c.menu_item_variant_type_id, c.price, c.size_id,
> c.parent_menu_item_variant_id, d.menu_item_variant_type_desc, e.size_desc,
> f.currency_code, a.image, a.mark_id, m.mark_name
>
> FROM menu_item_category AS b, menu_item_variant AS c, menu_item_variant_type
> AS d, item_size AS e, restaurant AS f, menu_item AS a
>
> LEFT OUTER JOIN mark AS m ON (a.mark_id = m.mark_id) WHERE
> a.menu_item_category_id = b.menu_item_category_id AND a.menu_item_id =
> c.menu_item_id AND c.menu_item_variant_type_id = d.menu_item_variant_type_id
> AND d.is_hidden = 'false' AND c.size_id = e.size_id AND a.restaurant_id =
> f.restaurant_id AND f.restaurant_id = 1528 AND (a.menu_item_category_id =
> NULL OR NULL IS NULL)
>
> AND c.menu_item_variant_id = (SELECT min(menu_item_variant_id) FROM
> menu_item_variant WHERE menu_item_id = a.menu_item_id AND deleted = 'N' limit
> 1) AND a.active = 'Y'
> AND (CONCAT_WS('', ',', a.hidden_branch_ids, ',') NOT LIKE CONCAT_WS('',
> '%,4191,%') OR NULL IS NULL)
> AND is_menu_item_available(a.menu_item_id, 'Y') = 'Y'
>
> ORDER BY a.row_order, menu_item_id;
>
>
> below is the plan
>
>
> Sort (cost=189.27..189.27 rows=1 width=152) (actual time=5.876..5.885
> rows=89 loops=1)
> " Sort Key: a.row_order, a.menu_item_id"
> Sort Method: quicksort Memory: 48kB
> -> Nested Loop Left Join (cost=5.19..189.26 rows=1 width=152) (actual
> time=0.188..5.809 rows=89 loops=1)
> Join Filter: (a.mark_id = m.mark_id)
> Rows Removed by Join Filter: 267
> -> Nested Loop (cost=5.19..
