Re: Need help on query optimization

[Saurabh Agrawal <mail@xxxxxxxxxxxxxxxxxx>]

Here B is a ltree column, E is a jsonb column.

It may also help to mention the indexes and their types. eg. Does column B have a GiST index?

 

EXPLAIN ANALYZE SELECT * FROM A

where ( B <@ 'INDIA' ) AND C = 'D'

AND CAST ( E->'F'->'G'->>'H' AS DATE ) >= '2021-02-01'

AND CAST ( E->'F'->'G'->>'H' AS DATE ) <= '2021-02-24'

ORDER BY E -> 'F' ->> 'J' ASC,created_date DESC

OFFSET 0 ROWS FETCH NEXT 200 ROWS ONLY

 

"Limit  (cost=22009.81..22010.08 rows=105 width=3853) (actual time=2295.654..2295.688 rows=200 loops=1)"

"  ->  Sort  (cost=22009.81..22010.08 rows=105 width=3853) (actual time=2295.651..2295.671 rows=200 loops=1)"

"        Sort Key: (((E -> 'F'::text) ->> 'J'::text)), created_date DESC"

"        Sort Method: top-N heapsort  Memory: 355kB"

"        ->  Index Scan using task_opp_tlmd_iscmp_idx on task  (cost=0.56..22006.29 rows=105 width=3853) (actual time=3.788..2277.503 rows=10982 loops=1)"

"              Index Cond: (C = 'D'::ltree)"

"              Filter: ((B <@ 'INDIA'::ltree) AND (((((E -> 'F'::text) -> 'G'::text) ->> 'H'::text))::date >= '2021-02-01'::date) AND (((((E -> 'F'::text) -> 'G'::text) ->> 'H::text))::date <= '2021-02-24'::date))"

"              Rows Removed by Filter: 14738"

"Planning Time: 0.418 ms"

"Execution Time: 2295.981 ms"

Thanks & Regards,

Shubham