On Wed, Jun 08, 2022 at 09:44:08AM +0100, Paulo Silva wrote: > But if I add an ORDER BY and a LIMIT something goes very wrong (Q2): A somewhat common problem. A common workaround is to change "ORDER BY a" to something like "ORDER BY a+0" (if your framework will allow it). > An EXPLAIN (ANALYZE, BUFFERS) for Q2 returns this: ... > -> Index Scan Backward using ix_ng_content_date on ng_content "Extent1" (cost=0.43..40616715.85 rows=2231839 width=12) (actual time=11027.808..183839.289 rows=5 loops=1) > Filter: ((2 = id_status) AND (date_from <= LOCALTIMESTAMP) AND (date_to >= LOCALTIMESTAMP) AND (SubPlan 1)) > Rows Removed by Filter: 4685618 > Buffers: shared hit=15414533 read=564480 written=504 I'm not sure if it would help your original issue, but the rowcount estimate here is bad - overestimating 2231839 rows instead of 5. Could you try to determine which of those conditions (id_status, date_from, date_to, or SubPlan) causes the mis-estimate, or if the estimate is only wrong when they're combined ? -- Justin
