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

On Fri, 28 May 2021 at 21:40, sreekanth vajrapu
<sreekanthvajrapu@gmail.com> wrote:
> 2) Version of the postgres is "PostgreSQL 9.5.21".
>
> Below are the execution plans along with the query and index definitions. Kindly help me to resolve this issue.

The following works ok for me all the way back to PostgreSQL 10.

create table t1 (id int primary key, name text not null, deleted bool);
create table t2 (id int primary key);
create table t3 (id int primary key);
create index on t1 (coalesce(deleted,false),name);

 explain (costs off)
SELECT item.*
FROM (
SELECT
          t1.name,
          t1.deleted
     FROM t1
     JOIN t2 USING (id)
     JOIN t3 USING (id)
) item
WHERE NOT COALESCE(deleted,false)
ORDER BY item.name LIMIT 31 OFFSET 0;
                             QUERY PLAN
--------------------------------------------------------------------
 Limit
   ->  Nested Loop
         Join Filter: (t1.id = t3.id)
         ->  Nested Loop
               ->  Index Scan using t1_coalesce_name_idx on t1
                     Index Cond: (COALESCE(deleted, false) = false)
               ->  Index Only Scan using t2_pkey on t2
                     Index Cond: (id = t1.id)
         ->  Index Only Scan using t3_pkey on t3
               Index Cond: (id = t2.id)
(10 rows)

However, I see it does not work on 9.5.  Something must have been
changed in 10 to allow the index to be used.  I don't really see any
indication of what that might be from the release notes and I'm too
lazy to git bisect to find out what the change was.

Either way, you should likely upgrade to a supported version of
PostgreSQL. 9.5 went out of support in Feb.  See:
https://www.postgresql.org/support/versioning/

If you need it to work on 9.5, you'll likely get it working if you use
a partial index:

create index on t1 (name) where not coalesce(deleted,false);

David.