Re: limits, indexes, views and query planner

On Mon, 15 Aug 2022 at 20:30, Marc Mamin <M(dot)Mamin(at)intershop(dot)de> wrote:
> in the example below, we can see that the view test_ab prevents the usage of the index to retrieve the top last rows.
> This is a special case, as the where clause excludes data from the second table, and the explain output do not references it at all.
> I wonder if the planner could be able to exclude the table_b earlier in its plan and to possibly fallback to a plan equivalent to the first one.
> with a view on a single table (test_av), the index is used.

I think you might be confusing UNION and UNION ALL. PostgreSQL is
under no obligation to uniquify the final result in your version of
the view with a single table, but it is when you UNION both tables
together, regardless of if one of the scans causes one of the union
branches to be removed or not.

Having said that, the planner still could do a better job of this as
it could have done Limit -> Unique -> Merge Append -> Index Scan. I
do have some work in progress code to rewrite the union planner so
that it able to request pre-sorted input to allow Merge Append to be
used rather than Unique -> Sort. I'm not sure if it'll help this case
or not.

David