Re: Query is slow when order by and limit clause are used in the query

On Mon, May 24, 2021 at 5:01 PM sreekanth vajrapu
<sreekanthvajrapu(at)gmail(dot)com> wrote:
>
> Hi Bharath,
>
> Thanks for the quick reply, I have attached the execution plan for below 3 scenarios. Our application is using 1st Scenario
>
> 1) WITH ORDER BY AND LIMIT 30 -- Very slow(3 to 5 seconds)
> 2) WITH ORDER BY WITHOUT LIMIT -- Very fast(160 MS)
> 3) WITH ORDER BY WITHOUT LIMIT 100 -- Very fast(160 MS)
>
> Kidney let me know if you need any more details on this.

I see that there are a huge number of Heap Fetches: 599354 with LIMIT
30 clause vs Heap Fetches: 11897 without LIMIT clause, maybe that
could be the reason for the slowness. I'm not sure why this is
happening with the LIMIT 30 clause only. Is it that this issue happens
every time? Say, if you run with LIMIT 30, then the query finishes in
3-5sec. Immediately if you run without a LIMIT clause then the query
completes in 160ms. Is vacuum running successfully on the tables and
indexes for which there's a huge number of heap fetches?

I have no further thoughts on this, other hackers may have better
suggestions though.

BTW, which version of postgresql are you using?

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com