Re: GROUP BY ROLLUP queries on views trigger full table scans (index usage not optimized)

On Thu, Dec 12, 2025 at 9:28 AM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> This seems to be a separate issue.  The query against the view chooses
> different indexes than the query against the underlying table.  This
> isn't the index capability issue you initially reported, but rather a
> cost estimation issue.

Hello Richard,

After testing, your patch can resolve the issue with the sample case I raised earlier.
Thank you.

However, there is another problem (I thought my example was sufficient, but it actually wasn’t).
In my actual scenario, my view includes join conditions,
and the new patch still does not achieve the same index behavior as in PostgreSQL 16.11.
So it might still require your help to look into what the issue is.
Thank you.

--- Regarding the attachment ---
Consistent with the original attachment: demo-v4-0001-partitioned_table_view_Test_Data_and_Examples.sql.txt
New example: demo-v4-0002-View_with_join_Examples.sql.txt

--- EXPLAIN ANALYZE ---

EXPLAIN ANALYZE from pg18.1 (Seq Scan on invoices_2020 and Rows Removed by Filter: 1000)

  ->  Hash Right Join  (cost=260.06..286.43 rows=289 width=20) (actual time=2.040..2.060 rows=116.00 loops=1)
        Hash Cond: ((materials2.material_id)::text = (invoices.material_code)::text)
        Buffers: shared hit=79
        ->  Seq Scan on materials2  (cost=0.00..14.90 rows=490 width=58) (actual time=0.012..0.013 rows=0.00 loops=1)
              Buffers: shared hit=1
        ->  Hash  (cost=258.59..258.59 rows=118 width=29) (actual time=2.022..2.024 rows=116.00 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 15kB
              Buffers: shared hit=78
              ->  Append  (cost=0.00..258.59 rows=118 width=29) (actual time=1.119..1.973 rows=116.00 loops=1)
                    Buffers: shared hit=78
                    ->  Seq Scan on invoices_2020 invoices_1  (cost=0.00..43.00 rows=1 width=29) (actual
time=0.243..0.243rows=0.00 loops=1) 
                          Filter: (((sales_org)::text = ANY ('{DF01,DG01}'::text[])) AND ((data_control_scope)::text =
'DD_OG'::text)AND ((valuation_order_flag)::text = '1'::text) AND ((approval_status)::text = 'y'::text) AND
(((document_date>= '2024-06-01'::date) AND (document_date <= '2024-12-31'::date)) OR ((document_date >=
'2025-01-01'::date)AND (document_date <= '2025-12-09'::date)))) 
                          Rows Removed by Filter: 1000
                          Buffers: shared hit=13
                    ->  Seq Scan on invoices_2021 invoices_2  (cost=0.00..43.00 rows=1 width=29) (actual
time=0.255..0.255rows=0.00 loops=1) 
                          Filter: (((sales_org)::text = ANY ('{DF01,DG01}'::text[])) AND ((data_control_scope)::text =
'DD_OG'::text)AND ((valuation_order_flag)::text = '1'::text) AND ((approval_status)::text = 'y'::text) AND
(((document_date>= '2024-06-01'::date) AND (document_date <= '2024-12-31'::date)) OR ((document_date >=
'2025-01-01'::date)AND (document_date <= '2025-12-09'::date)))) 
                          Rows Removed by Filter: 1000
                          Buffers: shared hit=13



EXPLAIN ANALYZE from pg16.11 (Recheck Cond: (((document_date >= '202)

  ->  Hash Right Join  (cost=108.69..128.52 rows=309 width=10) (actual time=0.235..0.248 rows=120 loops=1)
        Hash Cond: ((materials2.material_id)::text = (invoices.material_code)::text)
        ->  Seq Scan on materials2  (cost=0.00..14.90 rows=490 width=58) (actual time=0.005..0.005 rows=0 loops=1)
        ->  Hash  (cost=107.11..107.11 rows=126 width=19) (actual time=0.226..0.230 rows=120 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 15kB
              ->  Append  (cost=8.57..107.11 rows=126 width=19) (actual time=0.039..0.211 rows=120 loops=1)
                    ->  Bitmap Heap Scan on invoices_2020 invoices_1  (cost=8.57..12.60 rows=1 width=19) (actual
time=0.007..0.007rows=0 loops=1) 
                          Recheck Cond: (((document_date >= '2024-06-01'::date) AND (document_date <=
'2024-12-31'::date))OR ((document_date >= '2025-01-01'::date) AND (document_date <= '2025-12-09'::date))) 
                          Filter: (((sales_org)::text = ANY ('{DF01,DG01}'::text[])) AND ((data_control_scope)::text =
'DD_OG'::text)AND ((valuation_order_flag)::text = '1'::text) AND ((approval_status)::text = 'y'::text)) 
                          ->  BitmapOr  (cost=8.57..8.57 rows=1 width=0) (actual time=0.005..0.006 rows=0 loops=1)
                                ->  Bitmap Index Scan on idx_invoices_2020_date  (cost=0.00..4.29 rows=1 width=0)
(actualtime=0.004..0.004 rows=0 loops=1) 
                                      Index Cond: ((document_date >= '2024-06-01'::date) AND (document_date <=
'2024-12-31'::date))
                                ->  Bitmap Index Scan on idx_invoices_2020_date  (cost=0.00..4.29 rows=1 width=0)
(actualtime=0.001..0.001 rows=0 loops=1) 
                                      Index Cond: ((document_date >= '2025-01-01'::date) AND (document_date <=
'2025-12-09'::date))

Thanks & Best Regards
_________________________________________________________________________________
Haowu Ge (BG5FRG) | Homepage: https://www.gehaowu.com | PGP:7A06 1F6E DF09 D8A8