I am trying to execute the below query and getting below explain plan in postgres .
Please provide any inputs on the same , on how to optimize the same.
Here B is a ltree column, E is a jsonb column.
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
