Re: Need help on query optimization

>
>
> 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
>