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)
