Re: Query time related to limit clause

What's the definition for abc_serv_nch_q1_2021_expr_idx3? That is a jsonb
field I assume? Statistics aren't great on jsonb data, so you may benefit
greatly from pulling keys out to be stored as a standard column. I would be
curious for more "pure" estimates on each quarterly partition directly for
only the condition below (explain analyze, just looking at estimated vs
actual row counts) since they seem to be rather severe overestimates but
I'm not sure how much the date condition is obscuring that.

(((common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->>
'subGridName'::text) = '905811-22_MISCN_data'::text)"

The planner is choosing to use ONLY the index on the created timestamp when
limit is 1 and finding they nearly all match (I hope all on the q2
partition) and needs to filter almost all of those out (all from q1
partition I think, and nearly all from the others). I believe that the
planner thinks the other criteria in the query are not nearly as selective
as they are, and so it thinks it will find 1 match very quickly and be
done. That isn't the case.

When you want more rows, the planner decides that using both indexes is
less costly and it is correct.