Re: GROUP BY ROLLUP queries on views trigger full table scans (index usage not optimized)
| От | Haowu Ge |
|---|---|
| Тема | Re: GROUP BY ROLLUP queries on views trigger full table scans (index usage not optimized) |
| Дата | |
| Msg-id | eef00885-2b2a-4af2-b0b1-3ba04e93efe5.gehaowu@bitmoe.com обсуждение исходный текст |
| Ответ на | Re: GROUP BY ROLLUP queries on views trigger full table scans (index usage not optimized) (Richard Guo <guofenglinux@gmail.com>) |
| Ответы |
Re: GROUP BY ROLLUP queries on views trigger full table scans (index usage not optimized)
|
| Список | pgsql-bugs |
Hello Richard,
> Hmm, I don't have much insight into the installation issue. I tested
> the v2 patch on both v18 and master, and it fixes your query.
Based on the "Minimal Reproducible Example" principle,
I previously created a simplified version of the example,
which you successfully fixed. However, during actual testing,
I found that the behavior does not meet expectations.
Specifically, I observed inconsistent index behavior when using a view compared to querying the underlying table directly.
To help illustrate this issue, I used DeepSeek to generate a test case that closely mirrors my real-world database scenario.
As you can see:
- Approach 1 Does not use a view and uses GROUP BY ROLLUP. uses the date column as the indexed filtering field.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)))
- Approach 2 Uses a view and uses GROUP BY ROLLUP. uses columns "approval_status" and "data_control_scope" as indexed filtering fields, which unfortunately causes the query to scan all partitions of my partitioned table.Recheck Cond: (((approval_status)::text = 'y'::text) AND ((data_control_scope)::text = 'DD_OG'::text))
Please refer to the attached file for the detailed example.
------------------------------------------------------------------发件人：Richard Guo <guofenglinux@gmail.com>发送时间：2025年12月10日(周三) 10:02收件人："葛豪武"<gehaowu@bitmoe.com>抄 送："pgsql-bugs"<pgsql-bugs@lists.postgresql.org>; Tom Lane<tgl@sss.pgh.pa.us>主 题：Re: GROUP BY ROLLUP queries on views trigger full table scans (index usage not optimized)On Tue, Dec 9, 2025 at 5:54 PM Haowu Ge <gehaowu@bitmoe.com> wrote:
> I built it via dpkg-buildpackage,
> but I'm unable to generate a proper installation package with the v2 patch
Hmm, I don't have much insight into the installation issue. I tested
the v2 patch on both v18 and master, and it fixes your query.
EXPLAIN (COSTS OFF)
SELECT material_id, AVG(unit_price) as avg_price
FROM materials_view
WHERE material_id = 'PI'
GROUP BY ROLLUP(material_id);
QUERY PLAN
--------------------------------------------------------
GroupAggregate
Group Key: materials.material_id
Group Key: ()
-> Index Scan using materials_pkey on materials
Index Cond: ((material_id)::text = 'PI'::text)
(5 rows)
Regarding back-patching, I believe this issue exists before v18, but
it seems that the change in v18 made it common enough to notice,
especially in queries with grouping sets. Given the lack of reports
for versions prior to v18, I'm inclined to back-patch this only to
v18.
Any thoughts?
- Richard
Вложения
В списке pgsql-bugs по дате отправления: