Hi folks! I’ve a query where adding a rollup to the group by switches to
GroupAggregate unexpectedly, where the standard GROUP BY uses HashAggregate.
Since the rollup should only add one additional bucket, the switch to having to
sort (and thus a to-disk temporary file) is very puzzling. This reads like a
query optimiser bug to me. This is the first I’ve posted to the list, please
forgive me if I’ve omitted any “before bugging the list” homework.
Description: Adding a summary row by changing “GROUP BY x” into “GROUP BY
ROLLUP (x)” should not cause a switch from HashAggregate to GroupAggregate
Here’s the “explain” from the simple GROUP BY:
projectdb=> explain analyze verbose SELECT error_code, count ( * ) FROM
api_activities GROUP BY error_code;
QUERY PLAN
-
HashAggregate (cost=3456930.11..3456930.16 rows=5 width=2) (actual
time=26016.222..26016.223 rows=5 loops=1)
Output: error_code, count(*)
Group Key: api_activities.error_code
-> Seq Scan on public.api_activities (cost=0.00..3317425.74 rows=27900874
width=2) (actual time=0.018..16232.608 rows=36224844 loops=1)
Output: id, client_id, date_added, kind, activity, error_code
Planning time: 0.098 ms
Execution time: 26016.337 ms
(7 rows)
Changing this to a GROUP BY ROLLUP switches to GroupAggregate (with the
corresponding to-disk temporary table being created):
projectdb=> explain analyze verbose SELECT error_code, count ( * ) FROM
api_activities GROUP BY rollup (error_code);
QUERY PLAN
---
GroupAggregate (cost=7149357.90..7358614.52 rows=6 width=2) (actual
time=54271.725..82354.144 rows=6 loops=1)
Output: error_code, count(*)
Group Key: api_activities.error_code
Group Key: ()
-> Sort (cost=7149357.90..7219110.09 rows=27900874 width=2) (actual
time=54270.636..76651.121 rows=36222428 loops=1)
Output: error_code
Sort Key: api_activities.error_code
Sort Method: external merge Disk: 424864kB
-> Seq Scan on public.api_activities (cost=0.00..3317425.74
rows=27900874 width=2) (actual time=0.053..34282.239 rows=36222428 loops=1)
Output: error_code
Planning time: 2.611 ms
Execution time: 82437.416 ms
(12 rows)
I’ve given the output of “EXPLAIN ANAYLZE VERBOSE” rather than non-analyze, but
there was no difference in the plan.
Running VACUUM FULL ANALYZE on this table makes no difference. Switching to
Count(error_code) makes no difference. Using GROUP BY GROUPING SETS ((),
error_code) makes no difference.
I understand that a HashAggregate is possible only if it can fit all the
aggregates into work_mem. There are 5 different error codes, and the statistics
(from pg_stats) are showing that PG knows this. Adding just one more bucket for
the “()” case should not cause a fallback to GroupAggregate.
PostgreSQL version: 9.5.2 (just upgraded today, Thank you! <3 )
(Was exhibiting same problem under 9.5.0)
How installed: apt-get package from apt.postgresql.org
<http://apt.postgresql.org/>
Settings differences:
application_name: psql
client_encoding: UTF8
DateStyle: ISO, MDY
default_text_search_config: pg_catalog.english
dynamic_shared_memory_type: posix
lc_messages: en_US.UTF-8
lc_monetary: en_US.UTF-8
lc_numeric: en_US.UTF-8
lc_time: en_US.UTF-8
listen_addresses: *
log_line_prefix: %t [%p-%c-%l][%a][%i][%e][%s][%x-%v] %q%u@%d
log_timezone: UTC
logging_collector: on
max_connections: 100
max_stack_depth: 2MB
port: 5432
shared_buffers: 1GB
ssl: on
ssl_cert_file: /etc/ssl/certs/ssl-cert-snakeoil.pem
ssl_key_file: /etc/ssl/private/ssl-cert-snakeoil.key
TimeZone: UTC
work_mem: 128MB
OS and Version: Ubuntu Trusty: Linux 3.13.0-66-generic #108-Ubuntu SMP Wed Oct
7 15:20:27 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
Program used to connect: psql
Nothing unusual in the logs, apart from the query indicating that it took a
while to run.
I know that there’s several workarounds I can use for this simple case, such as
using a CTE, then doing a rollup on that, but I’m simply reporting what I think
is a bug in the query optimizer.
Thank you for your attention! Please let me know if there’s any additional
information you need, or additional tests you’d like to run.
— Chris Cogdon <ch...@cogdon.org <mailto:ch...@cogdon.org>>
— Using PostgreSQL since 6.2!
