Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1alg0P-00038E-OF for pgsql-performance@arkaria.postgresql.org; Thu, 31 Mar 2016 17:03:38 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1alg0P-0003Uv-9f for pgsql-performance@arkaria.postgresql.org; Thu, 31 Mar 2016 17:03:37 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1alg0O-0003UV-7F for pgsql-performance@postgresql.org; Thu, 31 Mar 2016 17:03:36 +0000 Received: from mail-pa0-x235.google.com ([2607:f8b0:400e:c03::235]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1alg0K-0000sk-G5 for pgsql-performance@postgresql.org; Thu, 31 Mar 2016 17:03:34 +0000 Received: by mail-pa0-x235.google.com with SMTP id tt10so70233030pab.3 for ; Thu, 31 Mar 2016 10:03:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cogdon-org.20150623.gappssmtp.com; s=20150623; h=from:subject:message-id:date:to:mime-version; bh=7BSADyTrNkrmX5AM0/lPYFGzlDEss66QQrB5jHcJ7io=; b=TqLZpb58L58US6VHTc97DmTc/BOm4DaifAmvwU6CjHOgoHWkB0oTyKjOI7yIgc6lah KjQPnEvKdrSD7FqUJsU7sJ8p8Ctfrjq3BmSm1/LnfApsHEzCAS0ewLeFkqzfEeWMNyN8 8DRvlub3eiIBLTJRrxTgFOnhq6yVoMs8LdRt09nbv3oD8S39rWlYn+e8skjpKr8bf2j0 bm3d0RQoP8UbH2khQlqbOl2dpk1MVQ426NmJw/BpIC+G7zfSWbuQ50FiO/e+k9pMi4A0 ZYuhLpaHnT51gbDvdWI1nW7g+rok8qRAFmrLLmmXbQDbJFOxF7OOym/w3V0Qx7i2AHn7 /Gtg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:from:subject:message-id:date:to:mime-version; bh=7BSADyTrNkrmX5AM0/lPYFGzlDEss66QQrB5jHcJ7io=; b=FfE1NfMd0CSOo+RXH82BFHfwQnYvvycfiwN+sjOL7XaYviW6WnIWl0GBKyNvMqf68R L9cBezLv5VeWogTxUEXG4XunxNFWZN6KQIRz9dyB8PT3OSlsFQ8fSpZW+MkN5tamxaEQ TuElZCqA0WWP2/mYOFPq5e2GrguWTKRbV7O+NFE7SlkDTXHxORQn5CWt/OHgag5tgUzH AKtfvn+23/7Lzkrs7wBjdNnpy4SN92If8KW/c6Ce8grGklKF1rDjJIruHCx74x0h4GOs sY7x44hsBEbu9qnIkOFvmDBZl2bgy8eilIIedvDolLsmZmRYgXphOSkkZMu9oFX67Zf7 bS4Q== X-Gm-Message-State: AD7BkJLDO14mHIG/WBobSztM+XnvqyyN1rEUcZRDPVAfGdD+wpYu/XlUeF9VNU5sxOVszw== X-Received: by 10.66.102.8 with SMTP id fk8mr23615236pab.12.1459443811175; Thu, 31 Mar 2016 10:03:31 -0700 (PDT) Received: from [172.16.1.21] ([50.255.40.241]) by smtp.gmail.com with ESMTPSA id l81sm14733426pfj.21.2016.03.31.10.03.28 for (version=TLSv1/SSLv3 cipher=OTHER); Thu, 31 Mar 2016 10:03:28 -0700 (PDT) From: Chris Cogdon Content-Type: multipart/alternative; boundary="Apple-Mail=_B0CA19EA-A7C4-4BC0-AD40-74834E62FAA0" Subject: Adding a ROLLUP switches to GroupAggregate unexpectedly Message-Id: <09FB46A1-B2CA-4C65-9959-D4D03C81EBB1@cogdon.org> Date: Thu, 31 Mar 2016 10:03:27 -0700 To: pgsql-performance@postgresql.org Mime-Version: 1.0 (Mac OS X Mail 8.2 \(2104\)) X-Mailer: Apple Mail (2.2104) X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --Apple-Mail=_B0CA19EA-A7C4-4BC0-AD40-74834E62FAA0 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hi folks! I=E2=80=99ve 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=E2=80=99ve posted to the list, please forgive me if I=E2=80=99= ve omitted any =E2=80=9Cbefore bugging the list=E2=80=9D homework. Description: Adding a summary row by changing =E2=80=9CGROUP BY x=E2=80=9D= into =E2=80=9CGROUP BY ROLLUP (x)=E2=80=9D should not cause a switch = from HashAggregate to GroupAggregate Here=E2=80=99s the =E2=80=9Cexplain=E2=80=9D from the simple GROUP BY: projectdb=3D> explain analyze verbose SELECT error_code, count ( * ) = FROM api_activities GROUP BY error_code; QUERY = PLAN =20 = --------------------------------------------------------------------------= ------------------------------------------------------------------- HashAggregate (cost=3D3456930.11..3456930.16 rows=3D5 width=3D2) = (actual time=3D26016.222..26016.223 rows=3D5 loops=3D1) Output: error_code, count(*) Group Key: api_activities.error_code -> Seq Scan on public.api_activities (cost=3D0.00..3317425.74 = rows=3D27900874 width=3D2) (actual time=3D0.018..16232.608 rows=3D36224844= loops=3D1) 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=3D> explain analyze verbose SELECT error_code, count ( * ) = FROM api_activities GROUP BY rollup (error_code); = QUERY PLAN = =20 = --------------------------------------------------------------------------= ------------------------------------------------------------------------- GroupAggregate (cost=3D7149357.90..7358614.52 rows=3D6 width=3D2) = (actual time=3D54271.725..82354.144 rows=3D6 loops=3D1) Output: error_code, count(*) Group Key: api_activities.error_code Group Key: () -> Sort (cost=3D7149357.90..7219110.09 rows=3D27900874 width=3D2) = (actual time=3D54270.636..76651.121 rows=3D36222428 loops=3D1) Output: error_code Sort Key: api_activities.error_code Sort Method: external merge Disk: 424864kB -> Seq Scan on public.api_activities (cost=3D0.00..3317425.74 = rows=3D27900874 width=3D2) (actual time=3D0.053..34282.239 rows=3D36222428= loops=3D1) Output: error_code Planning time: 2.611 ms Execution time: 82437.416 ms (12 rows) I=E2=80=99ve given the output of =E2=80=9CEXPLAIN ANAYLZE VERBOSE=E2=80=9D= 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 =E2=80=9C()=E2=80=9D 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 = 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=20 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=E2=80=99s several workarounds I can use for this = simple case, such as using a CTE, then doing a rollup on that, but I=E2=80= =99m simply reporting what I think is a bug in the query optimizer. Thank you for your attention! Please let me know if there=E2=80=99s any = additional information you need, or additional tests you=E2=80=99d like = to run. =E2=80=94 Chris Cogdon > =E2=80=94 Using PostgreSQL since 6.2!=20 --Apple-Mail=_B0CA19EA-A7C4-4BC0-AD40-74834E62FAA0 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Hi folks! I=E2=80=99ve 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=E2=80=99ve posted to the list, please forgive = me if I=E2=80=99ve omitted any =E2=80=9Cbefore bugging the list=E2=80=9D = homework.


Description: Adding a summary row by = changing =E2=80=9CGROUP BY x=E2=80=9D into =E2=80=9CGROUP BY ROLLUP = (x)=E2=80=9D should not cause a switch from HashAggregate to = GroupAggregate


Here=E2=80=99s the = =E2=80=9Cexplain=E2=80=9D from the simple GROUP BY:

projectdb=3D> explain analyze verbose SELECT error_code, = count ( * ) FROM api_activities GROUP BY error_code;
         =                     =                     =                 QUERY PLAN =                     =                     =                     =      
---------------------------------------------------------------= --------------------------------------------------------------------------= ----
 HashAggregate = (cost=3D3456930.11..3456930.16 rows=3D5 width=3D2) (actual = time=3D26016.222..26016.223 rows=3D5 loops=3D1)
   Output: error_code, count(*)
   Group Key: = api_activities.error_code
   ->  Seq Scan on = public.api_activities  (cost=3D0.00..3317425.74 rows=3D27900874 = width=3D2) (actual time=3D0.018..16232.608 rows=3D36224844 = loops=3D1)
   =       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=3D> explain analyze verbose SELECT error_code, = count ( * ) FROM api_activities GROUP BY rollup (error_code);
          =                     =                     =                   QUERY = PLAN                  =                     =                     =            
---------------------------------------------------------------= --------------------------------------------------------------------------= ----------
 GroupAggregate  (cost=3D7149357.90..7358614.52 = rows=3D6 width=3D2) (actual time=3D54271.725..82354.144 rows=3D6 = loops=3D1)
   = Output: error_code, count(*)
   Group Key: api_activities.error_code
   Group Key: ()
   ->  Sort  = (cost=3D7149357.90..7219110.09 rows=3D27900874 width=3D2) (actual = time=3D54270.636..76651.121 rows=3D36222428 loops=3D1)
         = Output: error_code
         Sort Key: = api_activities.error_code
         Sort Method: external = merge  Disk: 424864kB
         ->  Seq Scan on = public.api_activities  (cost=3D0.00..3317425.74 rows=3D27900874 = width=3D2) (actual time=3D0.053..34282.239 rows=3D36222428 = loops=3D1)
   =             Output: error_code
 Planning time: 2.611 = ms
 Execution time: = 82437.416 ms
(12 = rows)


I=E2=80=99ve given the output of = =E2=80=9CEXPLAIN ANAYLZE VERBOSE=E2=80=9D 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 =E2=80=9C()=E2=80=9D 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


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=E2=80=99s several workarounds I can use for this simple = case, such as using a CTE, then doing a rollup on that, but I=E2=80=99m = simply reporting what I think is a bug in the query optimizer.


Thank you for your attention! Please let me know if there=E2=80= =99s any additional information you need, or additional tests you=E2=80=99= d like to run.


=E2=80=94 Chris Cogdon = <chris@cogdon.org>
=E2=80=94 Using = PostgreSQL since 6.2! 




= --Apple-Mail=_B0CA19EA-A7C4-4BC0-AD40-74834E62FAA0--