limits, indexes, views and query planner

[Marc Mamin <M.Mamin@xxxxxxxxxxxx>]

 

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='' 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)