From: | Zakhar Shapurau <zak(at)zvs(dot)no> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Using GROUPING SETS with more than one set disables predicate pushdown? |
Date: | 2017-11-21 11:04:17 |
Message-ID: | 1511262257.3219920.1179611992.260EC9D9@webmail.messagingengine.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)zvs(dot)no
+47 407 54 397
From | Date | Subject | |
---|---|---|---|
Next Message | Henrik Cednert (Filmlance) | 2017-11-21 14:28:43 | pg_dump 3 times as slow after 8.4 -> 9.5 upgrade |
Previous Message | Matthew Hall | 2017-11-21 08:05:09 | Re: insert and query performance on big string table with pg_trgm |