On Mon, 15 Aug 2022 at 20:30, Marc Mamin <M.Mamin@xxxxxxxxxxxx> 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
