Em sex., 11 de jun. de 2021 às 13:59, Ayub Khan <ayub...@gmail.com>
escreveu:
> Pavan,
>
> In jProfiler , I see that most cpu is consumed when the Tomcat thread is
> stuck at PgPreparedStatement.execute. I am using version 42.2.16 of JDBC
> driver.
>
>
> Ranier,
>
> EXPLAIN ANALYZE
>
> 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'
>
> Nested Loop Left Join (cost=5.15..162.10 rows=1 width=148) (actual
> time=0.168..5.070 rows=89 loops=1)
> Join Filter: (a.mark_id = m.mark_id)
> Rows Removed by Join Filter: 267
> -> Nested Loop (cost=5.15..161.04 rows=1 width=144) (actual
> time=0.161..4.901 rows=89 loops=1)
> -> Nested Loop (cost=4.86..158.72 rows=1 width=148) (actual
> time=0.156..4.729 rows=89 loops=1)
> -> Nested Loop (cost=4.57..158.41 rows=1 width=140) (actual
> time=0.151..4.572 rows=89 loops=1)
> -> Nested Loop (cost=4.28..158.10 rows=1 width=132)
> (actual time=0.145..4.378 rows=89 loops=1)
> -> Nested Loop (cost=0.71..152.51 rows=1
> width=95) (actual time=0.121..3.334 rows=89 loops=1)
> -> Index Scan using menu_item_restaurant_id
> on menu_item a (cost=0.42..150.20 rows=1 width=83) (actual time=0.115..3.129
> 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.57..5.59 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.13..3.14 rows=1 width=8)
> (actual time=0.008..0.008 rows=1 loops=89)
> -> Aggregate (cost=3.13..3.14
> 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.11 rows=8 width=8)
> (actual time=0.003..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.001 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: 2.078 ms
> Execution Time: 5.141 ms
>
> My guess is a bad planner (or slow planner) because of wrong or
inconsistent parameters.
You must define precise parameters before calling a Prepared Statement or
Planner will try to guess to do the best.
But this is a simple "guess" and can be completely wrong.
regards,
Ranier Vilela
