view_rollup_bug_EXPLAIN_ANALYZE.txt

PostgreSQL 18.1 (Debian 18.1-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit

----------------------1--------------------------
# 5. Query the base table to compute the average unit price for material 'PI',using a simple GROUP BY (no rollup)
EXPLAIN ANALYZE
SELECT material_id, AVG(unit_price) as avg_price
FROM materials
WHERE material_id = 'PI'
GROUP BY material_id;

GroupAggregate  (cost=0.41..8.45 rows=1 width=40) (actual time=0.045..0.046 rows=1.00 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using idx_material_id on materials  (cost=0.41..8.43 rows=1 width=14) (actual time=0.032..0.033 rows=1.00 loops=1)
        Index Cond: ((material_id)::text = 'PI'::text)
        Index Searches: 1
        Buffers: shared hit=4
Planning Time: 0.113 ms
Execution Time: 0.084 ms


----------------------2-------------------------
# 6. Query the base table to compute the average unit price for material 'PI',using a simple GROUP BY (rollup)
EXPLAIN ANALYZE
SELECT material_id, AVG(unit_price) as avg_price
FROM materials
WHERE material_id = 'PI'
GROUP BY ROLLUP(material_id);

GroupAggregate  (cost=0.41..8.46 rows=2 width=40) (actual time=0.028..0.029 rows=2.00 loops=1)
  Group Key: material_id
  Group Key: ()
  Buffers: shared hit=4
  ->  Index Scan using idx_material_id on materials  (cost=0.41..8.43 rows=1 width=14) (actual time=0.019..0.020 rows=1.00 loops=1)
        Index Cond: ((material_id)::text = 'PI'::text)
        Index Searches: 1
        Buffers: shared hit=4
Planning Time: 0.090 ms
Execution Time: 0.055 ms


----------------------3-------------------------
# 7. Query the view table to compute the average unit price for material 'PI',using a simple GROUP BY (no rollup)
EXPLAIN ANALYZE
SELECT material_id, AVG(unit_price) as avg_price
FROM materials_view
WHERE material_id = 'PI'
GROUP BY
material_id;

GroupAggregate  (cost=0.41..8.45 rows=1 width=40) (actual time=0.019..0.019 rows=1.00 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using idx_material_id on materials  (cost=0.41..8.43 rows=1 width=14) (actual time=0.012..0.013 rows=1.00 loops=1)
        Index Cond: ((material_id)::text = 'PI'::text)
        Index Searches: 1
        Buffers: shared hit=4
Planning Time: 0.067 ms
Execution Time: 0.040 ms


----------------------4-------------------------
# 8. Query the view table to compute the average unit price for material 'PI',using a simple GROUP BY (rollup)

*Seq Scan*

EXPLAIN ANALYZE
SELECT material_id, AVG(unit_price) as avg_price
FROM materials_view
WHERE material_id = 'PI'
GROUP BY ROLLUP(material_id);

GroupAggregate  (cost=0.00..1097.39 rows=251 width=64) (actual time=3.983..3.985 rows=2.00 loops=1)
  Group Key: materials.material_id
  Group Key: ()
  Buffers: shared hit=468
  ->  Seq Scan on materials  (cost=0.00..1093.00 rows=250 width=48) (actual time=0.018..3.970 rows=1.00 loops=1)
        Filter: ((material_id)::text = 'PI'::text)
        Rows Removed by Filter: 49999
        Buffers: shared hit=468
Planning Time: 0.117 ms
Execution Time: 4.029 ms




=============================================
Pg16 still uses indexing:

PostgreSQL 16.10 (Debian 16.10-1.pgdg13+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit

EXPLAIN ANALYZE
SELECT material_id, AVG(unit_price) as avg_price
FROM materials_view
WHERE material_id = 'PI'
GROUP BY ROLLUP(material_id);


GroupAggregate  (cost=0.41..8.46 rows=2 width=40) (actual time=0.233..0.235 rows=2 loops=1)
  Group Key: materials.material_id
  Group Key: ()
  ->  Index Scan using idx_material_id on materials  (cost=0.41..8.43 rows=1 width=14) (actual time=0.157..0.159 rows=1 loops=1)
        Index Cond: ((material_id)::text = 'PI'::text)
Planning Time: 0.228 ms
Execution Time: 0.322 ms