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.