Testing how smart Postgres handles sub query aggregates

sub_query_test.sql

anon=# SELECT
anon-#   SUM(impressions)
anon-# FROM direct_delivery_items;
     sum
--------------
 544642902932
(1 row)

Time: 19853.941 ms            -- base line measurement
anon=# SELECT
  SUM(impressions)
FROM direct_delivery_items;
     sum
--------------
 544642902932
(1 row)

Time: 19779.166 ms            -- base line measurement


anon=# SELECT
  SUM(impressions) + SUM(impressions)
FROM direct_delivery_items;
   ?column?
---------------
 1089285805864
(1 row)

Time: 19611.613 ms           -- still single table scan



anon=# SELECT
  SUM(impressions) + SUM(impressions)
FROM direct_delivery_items;
   ?column?
---------------
 1089285805864
(1 row)

Time: 19295.732 ms   -- still single table scan



anon=# SELECT
anon-#   a + b
anon-# FROM
anon-#   (SELECT SUM(impressions) a FROM direct_delivery_items) t1,
anon-#   (SELECT SUM(impressions) b FROM direct_delivery_items) t2
anon-# ;
   ?column?
---------------
 1089285805864
(1 row)

Time: 40890.009 ms  -- seems like it runs it twice


anon=# SELECT
  a + b
FROM
  (SELECT SUM(impressions) a FROM direct_delivery_items) t1,
  (SELECT SUM(impressions) b FROM direct_delivery_items) t2
;
   ?column?
---------------
 1089285805864
(1 row)

Time: 39579.068 ms  -- still running it twice




/*************
Join Subquery
*************/

anon=# SELECT * FROM generate_series(1, 2);
 generate_series
-----------------
               1
               2
(2 rows)

Time: 0.240 ms


anon=#
anon=# SELECT
anon-#    t2.b
anon-# FROM generate_series(1, 2)
anon-# JOIN LATERAL (
anon(#     SELECT SUM(impressions) b FROM direct_delivery_items
anon(# ) t2 ON true;
      b
--------------
 544642902932
 544642902932
(2 rows)

Time: 19812.964 ms  -- no penalty, SUM runs once
     
     
anon=#
anon=# SELECT
anon-#    t2.b
anon-# FROM generate_series(1, 2)
anon-# JOIN (
anon(#     SELECT SUM(impressions) b FROM direct_delivery_items
anon(# ) t2 ON true;
      b
--------------
 544642902932
 544642902932
(2 rows)

Time: 19543.773 ms    -- no penalty, SUM runs once