Re: slow query - Mailing list pgsql-performance
| From | Ayub Khan |
|---|---|
| Subject | Re: slow query |
| Date | |
| Msg-id | CAHdeyE+Y2Fi92hhxc6u9vdLfaC_1GzRW=jFXBxnYJkrxwqiaUg@mail.gmail.com Whole thread Raw |
| In response to | slow query (Ayub Khan <ayub.hp@gmail.com>) |
| List | pgsql-performance |
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$;
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 <ayub.hp@gmail.com> 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 planSort (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) (actual time=0.000..0.001 rows=3 loops=89)
Planning Time: 1.510 ms
Execution Time: 5.972 ms
--------------------------------------------------------------------
Sun Certified Enterprise Architect 1.5Sun Certified Java Programmer 1.4
Microsoft Certified Systems Engineer 2000
http://in.linkedin.com/pub/ayub-khan/a/811/b81
mobile:+966-502674604
----------------------------------------------------------------------
It is proved that Hard Work and kowledge will get you close but attitude will get you there. However, it's the Love
of God that will put you over the top!!
It is proved that Hard Work and kowledge will get you close but attitude will get you there. However, it's the Love
of God that will put you over the top!!
pgsql-performance by date: