On Thu, Sep 2, 2021 at 3:16 PM Shubham Mittal <mittalshubha...@gmail.com> wrote:
> Hi , > > *Please help in optimizing this query. I need to actually generate reports > daily using this query.. It takes almost 15 to 20 min to execute this query > due to joins.. * > *Here common_details is a jsonB column.* > > SELECT T.order_id, > ( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' :: > text ) > ->> 'srType' :: text > AS > product, > ( ( T.common_details -> 'commonDetails' :: text ) -> 'bundle' :: > text ) > ->> 'mobileNumber' :: text > AS > msisdn, > Use jsonb_populate_recordset (or one of its siblings) to get rid of as many of these key-based value extraction operations as possible and build a table from the contents of the jsonb. Possibly into a temporary table to which you add indexes. David J.
