Thread: limits, indexes, views and query planner
hello,
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.
An oddity in the plan is the expected row count in "Append"(100001) ...
( tested on postgres 14 )
Regards,
Marc Mamin
create temp table table_a as (select * from generate_series(1,100000)x);
create temp table table_b as (select * from generate_series(1,100000)x);
create index i_a on table_a (x);
create index i_b on table_b (x);
analyze table_a;
analyze table_b;
CREATE VIEW test_ab AS
select 'a' as src, x from table_a
UNION
select 'b' as src, x from table_b
;
explain analyze select * from table_a order by x desc limit 10;
Limit (cost=0.29..0.60 rows=10 width=4) (actual time=0.056..0.060 rows=10 loops=1)
-> Index Only Scan Backward using i_a on table_a (cost=0.29..3050.29 rows=100000 width=4) (actual time=0.055..0.058 rows=10 loops=1)
Heap Fetches: 10
explain analyze select * from test_ab where src='a' order by x desc limit 10;
Limit (cost=17895.92..17895.94 rows=10 width=36) (actual time=89.678..89.681 rows=10 loops=1)
-> Sort (cost=17895.92..18145.92 rows=100001 width=36) (actual time=89.677..89.679 rows=10 loops=1)
Sort Key: table_a.x DESC
Sort Method: top-N heapsort Memory: 25kB
-> Unique (cost=13984.92..14734.92 rows=100001 width=36) (actual time=47.684..75.574 rows=100000 loops=1)
-> Sort (cost=13984.92..14234.92 rows=100001 width=36) (actual time=47.682..60.869 rows=100000 loops=1)
Sort Key: ('a'::text), table_a.x
Sort Method: external merge Disk: 1768kB
-> Append (cost=0.00..2943.01 rows=100001 width=36) (actual time=0.012..21.268 rows=100000 loops=1)
-> Seq Scan on table_a (cost=0.00..1443.00 rows=100000 width=36) (actual time=0.011..14.078 rows=100000 loops=1)
-> Result (cost=0.00..0.00 rows=0 width=36) (actual time=0.001..0.002 rows=0 loops=1)
One-Time Filter: false
Planning Time: 0.107 ms
Execution Time: 90.139 ms
CREATE VIEW test_av AS
select 'a' as src, x from table_a;
explain analyze select * from test_av order by x desc limit 10;
-> Index Only Scan Backward using i_a on table_a (cost=0.29..3050.29 rows=100000 width=36) (actual time=0.017..0.019 rows=10 loops=1)
On Mon, 15 Aug 2022 at 20:30, Marc Mamin <M.Mamin@intershop.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 referencesit 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 equivalentto 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