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

On Tue, 25 May 2021 at 02:19, Tomas Vondra
<tomas.vondra@enterprisedb.com> wrote:
> If I had to guess, I'd say this is a case of the usual LIMIT problem,
> where the optimizer assumes the matching rows are uniformly distributed
> in the input relation, when in reality it's "concentrated" at the end.

I'd guess that too. But hard to say due to the inconsistent
anonymisation of the plan,

> Hard to say, though, confirming it would require looking at the data
> more closely. The one thing I'd suggest is changing the xxxx_index to
> also include the "deleted" column, but it's a stab in the dark.

I'd say, providing xxxx_item and xxxxx_item are actually the same
table but just anonymised poorly, then an index such as:

create index on xxxx_item(COALESCE(deleted,false), name);

then change the query so instead of doing WHERE NOT deleted or deleted
is null;   do instead WHERE NOT COALESCE(deleted,false);

Without the query change then there's no hope of that index being used.

I think this would improve the situation as the LIMIT 30 plan is using
xxxxx_index to provide presorted results for the ORDER BY but can only
do index filtering on: (((NOT deleted) OR (deleted IS NULL)) AND
(SubPlan 6)).  So providing not too many rows are filtered out by
SubPlan 6, then that should reduce the Rows Removed by Filter.
However, if the majority of those rows are filtered out by Subplan 6,
then the index won't help much.

It would be nice if the schema was better designed so the deleted
column could only be true or false though.

sreekanth, for the future, you can use https://explain.depesz.com/ to
anonymise your queries. It'll do it in a consistent way that changes
the names of things in a consistent way that people can still follow.

David