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

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.

Thanks,
Sreekanth.

On Mon, May 24, 2021 at 4:33 PM Bharath Rupireddy <
bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:

> On Mon, May 24, 2021 at 4:19 PM sreekanth vajrapu
> <sreekanthvajrapu(at)gmail(dot)com> wrote:
> >
> >
> > Hi Team,
> >
> > We are having a slow query issue for one of our applications. We are
> seeing slowness(5 seconds) when we use both ORDER BY and LIMIT 30 clause
> whereas the same query is performing very good(200 MS) when using only
> ORDER BY clause. Also note that the query performed very fast(200 MS) when
> we increased LIMIT to 100 along with ORDER BY.
> >
> >
> > Can you please help us if there are any bugs related to this? OR Can
> someone kindly provide some solution to this issue?
>
> I think it's good to provide explain (analyze, buffers, costs,
> verbose, summary) of the query and a reproducible test case (if
> possible). It will be easier for those who investigate the issue.
>
> With Regards,
> Bharath Rupireddy.
> EnterpriseDB: http://www.enterprisedb.com
>

--
Thanks & Regards,
Sreekanth