Need help on query optimization

From: Shubham Mittal <mittalshubham30(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Need help on query optimization
Date: 2021-03-22 07:21:36
Message-ID: CA+ERcR_qRW_67+mcscODj5e5MdtzOUNqc1ju_EHFFuyxW6bsVg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi Team,

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrus 2021-03-22 07:25:26 Re: SV: Log files polluted with permission denied error messages after every 10 seconds
Previous Message Michael Paquier 2021-03-22 05:52:24 Re: PostgreSQL occasionally unable to rename WAL files (NTFS)