Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE foo (
   man_id   serial
 , grp      int NOT NULL  -- integer >> text
 , subgroup int NOT NULL
 , power    int NOT NULL
);

INSERT INTO foo (grp, subgroup, power)
SELECT (random() * 1000)::int                       -- ~ 1000 distinct groups
     , (random() *  100)::int                       -- ~ 100 distinct subgroups
     , CASE WHEN random() >= .5 THEN 1 ELSE -1 END
FROM   generate_series(1, 1000000);                 -- ~ 1M rows

INSERT INTO foo (grp, subgroup, power) VALUES
   (1, 101,  1), (1, 101, -1), (1, 101,  1)
 , (2, 101,  1), (2, 101,  1), (2, 101, -1)   -- control subgoup 101 returns true
 , (1, 102,  1), (1, 102, -1), (1, 102, -1)
 , (2, 102,  1), (2, 102,  1), (2, 102, -1);  -- control subgoup 102 returns false

ALTER TABLE foo ADD PRIMARY KEY (man_id);
CREATE INDEX ON foo (subgroup);
ALTER TABLE foo ALTER grp SET STATISTICS 500;
1000000 rows affected

12 rows affected

VACUUM FULL FREEZE ANALYZE foo;
CREATE OR REPLACE FUNCTION f_power_tie1(_subgrp int) RETURNS bool AS
$func$
SELECT count(DISTINCT total_power) = 1
FROM  (
   SELECT sum(power) AS total_power
   FROM   foo
   WHERE  subgroup = $1
   GROUP  BY grp
   ) sub
$func$  LANGUAGE sql;


CREATE OR REPLACE FUNCTION f_power_tie2(_subgrp int) RETURNS bool AS
$func$
SELECT count(*) = 1
FROM  (
   SELECT true
   FROM  (
      SELECT sum(power) AS total_power
      FROM   foo
      WHERE  subgroup = $1
      GROUP  BY grp
      ) sub2
   GROUP  BY total_power
   ) sub2
$func$  LANGUAGE sql;


CREATE OR REPLACE FUNCTION f_power_tie3(_subgrp int) RETURNS bool AS
$func$
SELECT count(*) OVER () = 1
FROM  (
   SELECT sum(power) AS total_power
   FROM   foo
   WHERE  subgroup = $1
   GROUP  BY grp
   ) sub
GROUP  BY total_power
LIMIT  1;
$func$  LANGUAGE sql;


CREATE OR REPLACE FUNCTION f_power_tie4(_subgrp int) RETURNS bool AS
$func$
(
SELECT FALSE
FROM  (
   SELECT sum(power) AS total_power
   FROM   foo
   WHERE  subgroup = $1
   GROUP  BY grp
   ) sub
GROUP  BY total_power
OFFSET 1
LIMIT  1
)
UNION ALL
SELECT TRUE
LIMIT 1
$func$  LANGUAGE sql;


CREATE OR REPLACE FUNCTION f_power_tie5(_subgrp int) RETURNS bool AS
$func$
SELECT min(total_power) = max(total_power)  -- fails for NULL values!
FROM  (
   SELECT sum(power) AS total_power
   FROM   foo
   WHERE  subgroup = $1
   GROUP  BY grp
   ) sub
$func$  LANGUAGE sql;
-- all working as expected? (plus prewarm cache)
          SELECT f_power_tie1(101), f_power_tie1(102), f_power_tie1(66)   -- all true, false, ?
UNION ALL SELECT f_power_tie2(101), f_power_tie2(102), f_power_tie2(66)
UNION ALL SELECT f_power_tie3(101), f_power_tie3(102), f_power_tie3(66)
UNION ALL SELECT f_power_tie4(101), f_power_tie4(102), f_power_tie4(66)
UNION ALL SELECT f_power_tie5(101), f_power_tie5(102), f_power_tie5(66);


f_power_tie1
f_power_tie1
f_power_tie1


t
f
f

t
f
f

t
f
f

t
f
f

t
f
f


EXPLAIN (ANALYZE, TIMING OFF) SELECT f_power_tie1(66);


QUERY PLAN


Result  (cost=0.00..0.26 rows=1 width=1) (actual rows=1 loops=1)

Planning time: 0.062 ms

Execution time: 17.304 ms


EXPLAIN (ANALYZE, TIMING OFF) SELECT f_power_tie2(66);


QUERY PLAN


Result  (cost=0.00..0.26 rows=1 width=1) (actual rows=1 loops=1)

Planning time: 0.083 ms

Execution time: 17.010 ms


EXPLAIN (ANALYZE, TIMING OFF) SELECT f_power_tie3(66);


QUERY PLAN


Result  (cost=0.00..0.26 rows=1 width=1) (actual rows=1 loops=1)

Planning time: 0.082 ms

Execution time: 17.228 ms


EXPLAIN (ANALYZE, TIMING OFF) SELECT f_power_tie4(66);


QUERY PLAN


Result  (cost=0.00..0.26 rows=1 width=1) (actual rows=1 loops=1)

Planning time: 0.093 ms

Execution time: 16.970 ms


EXPLAIN (ANALYZE, TIMING OFF) SELECT f_power_tie5(66);


QUERY PLAN


Result  (cost=0.00..0.26 rows=1 width=1) (actual rows=1 loops=1)

Planning time: 0.079 ms

Execution time: 16.787 ms


-- execute query without func wrapper to see plan
EXPLAIN ANALYZE
SELECT count(DISTINCT total_power) = 1
FROM  (
   SELECT sum(power) AS total_power
   FROM   foo
   WHERE  subgroup = 66
   GROUP  BY grp
   ) sub;


QUERY PLAN


Aggregate  (cost=5885.43..5885.44 rows=1 width=1) (actual time=18.091..18.091 rows=1 loops=1)

  ->  HashAggregate  (cost=5862.90..5872.91 rows=1001 width=12) (actual time=17.709..17.919 rows=1001 loops=1)

        Group Key: foo.grp

        ->  Bitmap Heap Scan on foo  (cost=190.29..5812.67 rows=10047 width=8) (actual time=1.476..14.793 rows=10059 loops=1)

              Recheck Cond: (subgroup = 66)

              Heap Blocks: exact=4564

              ->  Bitmap Index Scan on foo_subgroup_idx  (cost=0.00..187.78 rows=10047 width=0) (actual time=0.875..0.875 rows=10059 loops=1)

                    Index Cond: (subgroup = 66)

Planning time: 0.099 ms

Execution time: 18.133 ms


EXPLAIN ANALYZE
SELECT count(*) = 1
FROM  (
   SELECT true
   FROM  (
      SELECT sum(power) AS total_power
      FROM   foo
      WHERE  subgroup = 66
      GROUP  BY grp
      ) sub2
   GROUP  BY total_power
   ) sub2;


QUERY PLAN


Aggregate  (cost=5940.31..5940.33 rows=1 width=1) (actual time=18.606..18.606 rows=1 loops=1)

  ->  Group  (cost=5932.81..5937.81 rows=200 width=9) (actual time=18.425..18.601 rows=21 loops=1)

        Group Key: sub2.total_power

        ->  Sort  (cost=5932.81..5935.31 rows=1001 width=8) (actual time=18.424..18.501 rows=1001 loops=1)

              Sort Key: sub2.total_power

              Sort Method: quicksort  Memory: 71kB

              ->  Subquery Scan on sub2  (cost=5862.90..5882.92 rows=1001 width=8) (actual time=17.957..18.261 rows=1001 loops=1)

                    ->  HashAggregate  (cost=5862.90..5872.91 rows=1001 width=12) (actual time=17.956..18.120 rows=1001 loops=1)

                          Group Key: foo.grp

                          ->  Bitmap Heap Scan on foo  (cost=190.29..5812.67 rows=10047 width=8) (actual time=1.473..15.030 rows=10059 loops=1)

                                Recheck Cond: (subgroup = 66)

                                Heap Blocks: exact=4564

                                ->  Bitmap Index Scan on foo_subgroup_idx  (cost=0.00..187.78 rows=10047 width=0) (actual time=0.882..0.882 rows=10059 loops=1)

                                      Index Cond: (subgroup = 66)

Planning time: 0.104 ms

Execution time: 18.651 ms


EXPLAIN ANALYZE
SELECT count(*) OVER () = 1
FROM  (
   SELECT sum(power) AS total_power
   FROM   foo
   WHERE  subgroup = 66
   GROUP  BY grp
   ) sub
GROUP  BY total_power
LIMIT  1;


QUERY PLAN


Limit  (cost=5932.81..5932.85 rows=1 width=9) (actual time=18.785..18.785 rows=1 loops=1)

  ->  WindowAgg  (cost=5932.81..5940.81 rows=200 width=9) (actual time=18.784..18.784 rows=1 loops=1)

        ->  Group  (cost=5932.81..5937.81 rows=200 width=8) (actual time=18.597..18.774 rows=21 loops=1)

              Group Key: sub.total_power

              ->  Sort  (cost=5932.81..5935.31 rows=1001 width=8) (actual time=18.595..18.673 rows=1001 loops=1)

                    Sort Key: sub.total_power

                    Sort Method: quicksort  Memory: 71kB

                    ->  Subquery Scan on sub  (cost=5862.90..5882.92 rows=1001 width=8) (actual time=18.058..18.413 rows=1001 loops=1)

                          ->  HashAggregate  (cost=5862.90..5872.91 rows=1001 width=12) (actual time=18.057..18.246 rows=1001 loops=1)

                                Group Key: foo.grp

                                ->  Bitmap Heap Scan on foo  (cost=190.29..5812.67 rows=10047 width=8) (actual time=1.448..15.147 rows=10059 loops=1)

                                      Recheck Cond: (subgroup = 66)

                                      Heap Blocks: exact=4564

                                      ->  Bitmap Index Scan on foo_subgroup_idx  (cost=0.00..187.78 rows=10047 width=0) (actual time=0.846..0.846 rows=10059 loops=1)

                                            Index Cond: (subgroup = 66)

Planning time: 0.107 ms

Execution time: 18.838 ms


EXPLAIN ANALYZE
(
SELECT FALSE
FROM  (
   SELECT sum(power) AS total_power
   FROM   foo
   WHERE  subgroup = 66
   GROUP  BY grp
   ) sub
GROUP  BY total_power
OFFSET 1
LIMIT  1
)
UNION ALL
SELECT TRUE
LIMIT 1;


QUERY PLAN


Limit  (cost=5932.83..5932.86 rows=1 width=1) (actual time=18.482..18.483 rows=1 loops=1)

  ->  Append  (cost=5932.83..5932.89 rows=2 width=1) (actual time=18.482..18.482 rows=1 loops=1)

        ->  Subquery Scan on "*SELECT* 1"  (cost=5932.83..5932.87 rows=1 width=1) (actual time=18.481..18.481 rows=1 loops=1)

              ->  Limit  (cost=5932.83..5932.86 rows=1 width=9) (actual time=18.481..18.481 rows=1 loops=1)

                    ->  Group  (cost=5932.81..5937.81 rows=200 width=9) (actual time=18.478..18.480 rows=2 loops=1)

                          Group Key: sub.total_power

                          ->  Sort  (cost=5932.81..5935.31 rows=1001 width=8) (actual time=18.477..18.477 rows=3 loops=1)

                                Sort Key: sub.total_power

                                Sort Method: quicksort  Memory: 71kB

                                ->  Subquery Scan on sub  (cost=5862.90..5882.92 rows=1001 width=8) (actual time=18.004..18.314 rows=1001 loops=1)

                                      ->  HashAggregate  (cost=5862.90..5872.91 rows=1001 width=12) (actual time=18.003..18.168 rows=1001 loops=1)

                                            Group Key: foo.grp

                                            ->  Bitmap Heap Scan on foo  (cost=190.29..5812.67 rows=10047 width=8) (actual time=1.452..15.077 rows=10059 loops=1)

                                                  Recheck Cond: (subgroup = 66)

                                                  Heap Blocks: exact=4564

                                                  ->  Bitmap Index Scan on foo_subgroup_idx  (cost=0.00..187.78 rows=10047 width=0) (actual time=0.860..0.860 rows=10059 loops=1)

                                                        Index Cond: (subgroup = 66)

        ->  Result  (cost=0.00..0.01 rows=1 width=1) (never executed)

Planning time: 0.125 ms

Execution time: 18.530 ms


EXPLAIN ANALYZE
SELECT min(total_power) = max(total_power)  -- fails for NULL values!
FROM  (
   SELECT sum(power) AS total_power
   FROM   foo
   WHERE  subgroup = 66
   GROUP  BY grp
   ) sub;


QUERY PLAN


Aggregate  (cost=5887.93..5887.94 rows=1 width=1) (actual time=17.980..17.980 rows=1 loops=1)

  ->  HashAggregate  (cost=5862.90..5872.91 rows=1001 width=12) (actual time=17.697..17.858 rows=1001 loops=1)

        Group Key: foo.grp

        ->  Bitmap Heap Scan on foo  (cost=190.29..5812.67 rows=10047 width=8) (actual time=1.495..14.800 rows=10059 loops=1)

              Recheck Cond: (subgroup = 66)

              Heap Blocks: exact=4564

              ->  Bitmap Index Scan on foo_subgroup_idx  (cost=0.00..187.78 rows=10047 width=0) (actual time=0.891..0.891 rows=10059 loops=1)

                    Index Cond: (subgroup = 66)

Planning time: 0.097 ms

Execution time: 18.025 ms