From: Zakhar Shapurau <zak@zvs.no>
To: pgsql-performance@postgresql.org
Subject: Using GROUPING SETS with more than one set disables predicate pushdown?
Date: Tue, 21 Nov 2017 12:04:17 +0100
Message-ID: <1511262257.3219920.1179611992.260EC9D9@webmail.messagingengine.com> (raw)
Hi!
First of all, thanks for the great work! PostgreSQL is amazing, and
community is super helpful.
I found an unexpected behaviour in PostgreSQL, and was advised to post
it to the performance mailing list on IRC.
Using GROUPING SETS with more than one set disables predicate pushdown?
Version:
PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
Seems like when GROUPING SETS with at least two sets are used in the
subquery, planner
can not push WHERE clauses inside.
Here are two queries that (I think) are equivalent, but produce very
different execution
plans leading to bad performance on real data - and in effect,
making it impossible to abstract away non-trivial grouping logic into a
view.
It might as well be that queries are not really equivalent, but I don't
see how.
Same problem happens even if grouping sets are the same - like `GROUPING
SETS ((), ())`.
CREATE TEMPORARY TABLE test_gs (
x INT,
y INT,
z INT,
PRIMARY KEY (x, y, z)
);
EXPLAIN
SELECT
x,
y,
avg(z) AS mean
FROM test_gs
WHERE x = 1
GROUP BY x, GROUPING SETS ((y), ());
QUERY PLAN
-----------------------------------------------------------------------------------------
GroupAggregate (cost=0.15..8.65 rows=20 width=40)
Group Key: x, y
Group Key: x
-> Index Only Scan using test_gs_pkey on test_gs (cost=0.15..8.33
rows=10 width=12)
Index Cond: (x = 1)
(5 rows)
EXPLAIN
SELECT x, y, mean
FROM (
SELECT
x,
y,
avg(z) AS mean
FROM test_gs
GROUP BY x, GROUPING SETS ((y), ())
) AS g
WHERE x = 1;
QUERY PLAN
--------------------------------------------------------------------------------------------
GroupAggregate (cost=0.15..62.10 rows=404 width=40)
Group Key: test_gs.x, test_gs.y
Group Key: test_gs.x
Filter: (test_gs.x = 1)
-> Index Only Scan using test_gs_pkey on test_gs (cost=0.15..41.75
rows=2040 width=12)
(5 rows)
The issue here is that the second query is not using index to filter on
x = 1 , instead it reads all the tuples from an index and applies the
filter.
Here is also a description in gist:
https://gist.github.com/zeveshe/cf92c9d2a6b14518af3180113e767ae7
Thanks a lot!
--
Zakhar Shapurau
zak@zvs.no
+47 407 54 397
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: zak@zvs.no
Subject: Re: Using GROUPING SETS with more than one set disables predicate pushdown?
In-Reply-To: <1511262257.3219920.1179611992.260EC9D9@webmail.messagingengine.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox