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

Thank you David for the quick response.

create index on t1 (name) where not coalesce(deleted,false) worked out and
execution time reduced to 900MS from 1200MS.

So I recommended the application team to upgrade to higher Postgresql
versions.

Thank you so much for the help and guidance.

Thanks.
Sreekanth.

On Fri, May 28, 2021 at 6:39 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Fri, 28 May 2021 at 21:40, sreekanth vajrapu
> <sreekanthvajrapu(at)gmail(dot)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.
>

--
Thanks & Regards,
Sreekanth