pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: David Fetter <david@fetter.org>
To: sreekanth vajrapu <sreekanthvajrapu@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Query is slow when order by and limit clause are used in the query
Date: Mon, 31 May 2021 16:41:05 +0000
Message-ID: <20210531164104.GA26722@fetter.org> (raw)
In-Reply-To: <CAKPbTYioxLr=yv_iZfP2AHs3VeM_v8njgnBM5DofDpyhMTtBmQ@mail.gmail.com>
References: <CAKPbTYioxLr=yv_iZfP2AHs3VeM_v8njgnBM5DofDpyhMTtBmQ@mail.gmail.com>

On Mon, May 24, 2021 at 04:19:01PM +0530, sreekanth vajrapu 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?

This is not at base a bug. Instead, it's a behavior which compliance
with the SQL standard mandates. You're doing pagination, and
unfortunately you're doing it in a way that, while it appears simple
and intuitive, guarantees poor performance for later pages.

Here's a concise description of the fundamental problem you're
encountering along with some suggestions as to how to do this more
efficiently, i.e. faster consistently.

https://use-the-index-luke.com/no-offset

Here are some more references on pagination and how to do it
efficiently:

http://www.depesz.com/2007/08/29/better-results-paging-in-postgresql-82/
https://wiki.postgresql.org/wiki/File:Pagination_Done_the_PostgreSQL_Way.pdf
https://coderwall.com/p/lkcaag
https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/
https://ask.use-the-index-luke.com/questions/205/how-to-query-for-previous-page-with-keyset-paginati...

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate





view thread (10+ messages)

Message-ID: <20210531164104.GA26722@fetter.org>
Permalink:  ../20210531164104.GA26722@fetter.org/
Also on:    postgresql.org/message-id/20210531164104.GA26722@fetter.org

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: david@fetter.org, sreekanthvajrapu@gmail.com, pgsql-bugs@lists.postgresql.org
  Subject: Re: Query is slow when order by and limit clause are used in the query
  In-Reply-To: <20210531164104.GA26722@fetter.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox