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.