Date: Wed, 8 Jun 2022 09:32:47 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Paulo Silva <paulojjs@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Strange behavior of limit clause in complex query
Message-ID: <20220608143247.GO29853@telsasoft.com>
References: 
 <CAHJdQrn8CKKgRUXxUpPBRa2x-jF1L-c+uLtYBS=R+oz1=TNtqA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAHJdQrn8CKKgRUXxUpPBRa2x-jF1L-c+uLtYBS=R+oz1=TNtqA@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20220608143247.GO29853%40telsasoft.com>
Precedence: bulk

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