| 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. 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 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 <chris@xxxxxxxxxx> — Using PostgreSQL since 6.2! |
