pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing GROUPING SETS with more than one set disables predicate pushdown?
3+ messages / 3 participants
[nested] [flat]
* Using GROUPING SETS with more than one set disables predicate pushdown?
@ 2017-11-21 11:04 Zakhar Shapurau <zak@zvs.no>
0 siblings, 1 reply; 3+ messages in thread
From: Zakhar Shapurau @ 2017-11-21 11:04 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Using GROUPING SETS with more than one set disables predicate pushdown?
@ 2017-11-21 14:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Zakhar Shapurau <zak@zvs.no>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2017-11-21 14:49 UTC (permalink / raw)
To: Zakhar Shapurau <zak@zvs.no>; +Cc: pgsql-performance
Zakhar Shapurau <zak@zvs.no> writes:
> Using GROUPING SETS with more than one set disables predicate pushdown?
It looks like this is a case that no one's gotten round to yet.
The comment in the relevant code is
* In some cases we may want to transfer a HAVING clause into WHERE. We
* cannot do so if the HAVING clause contains aggregates (obviously) or
* volatile functions (since a HAVING clause is supposed to be executed
* only once per group). We also can't do this if there are any nonempty
* grouping sets; moving such a clause into WHERE would potentially change
* the results, if any referenced column isn't present in all the grouping
* sets. (If there are only empty grouping sets, then the HAVING clause
* must be degenerate as discussed below.)
Presumably, we could examine the grouping sets to identify column(s)
present in all sets, and then allow the optimization for clauses that
reference only such columns. Or maybe I'm misreading the comment
(but then it needs clarification).
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Using GROUPING SETS with more than one set disables predicate pushdown?
@ 2017-11-21 18:16 Andres Freund <andres@anarazel.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Andres Freund @ 2017-11-21 18:16 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org,Tom Lane <tgl@sss.pgh.pa.us>; Zakhar Shapurau <zak@zvs.no>; +Cc: pgsql-performance
On November 21, 2017 6:49:26 AM PST, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>Zakhar Shapurau <zak@zvs.no> writes:
>
>Presumably, we could examine the grouping sets to identify column(s)
>present in all sets, and then allow the optimization for clauses that
>reference only such columns. Or maybe I'm misreading the comment
>(but then it needs clarification).
By memory that sounds about right. IIRC we'd some slightly more elaborate logic when GS were introduced, but had to take it out as buggy, and it was too late in the development cycle to come up with something better.
Andres
Andres
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-11-21 18:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-11-21 11:04 Using GROUPING SETS with more than one set disables predicate pushdown? Zakhar Shapurau <zak@zvs.no>
2017-11-21 14:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-11-21 18:16 ` Andres Freund <andres@anarazel.de>
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