Postgres 13

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 bug_snapshots(
  fixin_id   integer
, created_on timestamp
, pain       integer
, status_id  integer
);

INSERT INTO bug_snapshots(fixin_id, created_on, pain, status_id)
SELECT (random() * 10000)::int
     , '2012-03-01'::timestamp + interval '3 min' * (g + random())
     , g%1100 AS pain            -- some > 999
     , g%10   AS status_id       -- only few different values
FROM generate_series(1,300000) g;

ALTER TABLE bug_snapshots ADD PRIMARY KEY (fixin_id, created_on);

CREATE INDEX bug_snapshots_part_idx1 ON bug_snapshots (fixin_id, created_on, pain)
WHERE  status_id IN (2, 7, 5, 3)
AND    pain < 999;

CREATE INDEX bug_snapshots_part_idx2 ON bug_snapshots (created_on, fixin_id, pain)
WHERE  status_id IN (2, 7, 5, 3)
AND    pain < 999;               -- leading cols reversed
300000 rows affected

VACUUM ANALYZE bug_snapshots;
-- JOIN 1
EXPLAIN ANALYZE
SELECT created_on, sum(pain) AS sum_pain
-- FROM   unnest('{11,12,33,…,5351}'::int[]) AS f(fixin_id)
FROM   generate_series(2000, 3000, 1) AS g(fixin_id)  --   -- fake unest
JOIN   bug_snapshots USING (fixin_id)
WHERE  status_id IN (2, 7, 5, 3)
AND    created_on >= '2013-10-08 16:42:26.994994-0700'::timestamptz
AND    created_on <= '2013-11-07 15:42:26.994994-0800'::timestamptz
AND    pain < 999
GROUP  BY created_on
ORDER  BY created_on;


QUERY PLAN


GroupAggregate  (cost=434.70..443.98 rows=530 width=16) (actual time=3.470..3.718 rows=558 loops=1)

  Group Key: bug_snapshots.created_on

  ->  Sort  (cost=434.70..436.03 rows=530 width=12) (actual time=3.463..3.509 rows=558 loops=1)

        Sort Key: bug_snapshots.created_on

        Sort Method: quicksort  Memory: 51kB

        ->  Hash Join  (cost=255.27..410.72 rows=530 width=12) (actual time=2.992..3.333 rows=558 loops=1)

              Hash Cond: (g.fixin_id = bug_snapshots.fixin_id)

              ->  Function Scan on generate_series g  (cost=0.00..10.01 rows=1001 width=4) (actual time=0.077..0.153 rows=1001 loops=1)

              ->  Hash  (cost=189.56..189.56 rows=5257 width=16) (actual time=2.893..2.894 rows=5232 loops=1)

                    Buckets: 8192  Batches: 1  Memory Usage: 310kB

                    ->  Index Only Scan using bug_snapshots_part_idx2 on bug_snapshots  (cost=0.42..189.56 rows=5257 width=16) (actual time=0.063..2.059 rows=5232 loops=1)

                          Index Cond: ((created_on >= '2013-10-09 00:42:26.994994+01'::timestamp with time zone) AND (created_on <= '2013-11-07 23:42:26.994994+00'::timestamp with time zone))

                          Heap Fetches: 0

Planning Time: 0.612 ms

Execution Time: 3.857 ms


-- IN 1
EXPLAIN ANALYZE
SELECT created_on, sum(pain) AS sum_pain
FROM   bug_snapshots
WHERE  fixin_id IN (SELECT generate_series(2000, 3000, 1))  -- fake IN
AND    status_id IN (2, 7, 5, 3)
AND    created_on >= '2013-10-08 16:42:26.994994-0700'::timestamptz
AND    created_on <= '2013-11-07 15:42:26.994994-0800'::timestamptz
AND    pain < 999
GROUP  BY created_on
ORDER  BY created_on;


QUERY PLAN


GroupAggregate  (cost=255.27..264.55 rows=530 width=16) (actual time=3.135..3.384 rows=558 loops=1)

  Group Key: bug_snapshots.created_on

  ->  Sort  (cost=255.27..256.60 rows=530 width=12) (actual time=3.130..3.177 rows=558 loops=1)

        Sort Key: bug_snapshots.created_on

        Sort Method: quicksort  Memory: 51kB

        ->  Hash Join  (cost=22.45..231.29 rows=530 width=12) (actual time=0.706..3.052 rows=558 loops=1)

              Hash Cond: (bug_snapshots.fixin_id = (generate_series(2000, 3000, 1)))

              ->  Index Only Scan using bug_snapshots_part_idx2 on bug_snapshots  (cost=0.42..189.56 rows=5257 width=16) (actual time=0.034..1.734 rows=5232 loops=1)

                    Index Cond: ((created_on >= '2013-10-09 00:42:26.994994+01'::timestamp with time zone) AND (created_on <= '2013-11-07 23:42:26.994994+00'::timestamp with time zone))

                    Heap Fetches: 0

              ->  Hash  (cost=19.54..19.54 rows=200 width=4) (actual time=0.663..0.664 rows=1001 loops=1)

                    Buckets: 1024  Batches: 1  Memory Usage: 44kB

                    ->  HashAggregate  (cost=17.54..19.54 rows=200 width=4) (actual time=0.367..0.526 rows=1001 loops=1)

                          Group Key: generate_series(2000, 3000, 1)

                          Batches: 1  Memory Usage: 145kB

                          ->  ProjectSet  (cost=0.00..5.02 rows=1001 width=4) (actual time=0.002..0.104 rows=1001 loops=1)

                                ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

Planning Time: 0.448 ms

Execution Time: 3.451 ms


-- remove idx2!
DROP INDEX bug_snapshots_part_idx2;
-- JOIN 2
EXPLAIN ANALYZE
SELECT created_on, sum(pain) AS sum_pain
--FROM   unnest('{11,12,33,…,5351}'::int[]) AS f(fixin_id)
FROM   generate_series(2000, 3000, 1) AS g(fixin_id)  --   -- fake unest
JOIN   bug_snapshots USING (fixin_id)
WHERE  status_id IN (2, 7, 5, 3)
AND    created_on >= '2013-10-08 16:42:26.994994-0700'::timestamptz
AND    created_on <= '2013-11-07 15:42:26.994994-0800'::timestamptz
AND    pain < 999
GROUP  BY created_on
ORDER  BY created_on;


QUERY PLAN


GroupAggregate  (cost=2568.44..2577.72 rows=530 width=16) (actual time=4.724..4.972 rows=558 loops=1)

  Group Key: bug_snapshots.created_on

  ->  Sort  (cost=2568.44..2569.77 rows=530 width=12) (actual time=4.719..4.765 rows=558 loops=1)

        Sort Key: bug_snapshots.created_on

        Sort Method: quicksort  Memory: 51kB

        ->  Nested Loop  (cost=0.42..2544.46 rows=530 width=12) (actual time=0.133..4.497 rows=558 loops=1)

              ->  Function Scan on generate_series g  (cost=0.00..10.01 rows=1001 width=4) (actual time=0.076..0.229 rows=1001 loops=1)

              ->  Index Only Scan using bug_snapshots_part_idx1 on bug_snapshots  (cost=0.42..2.52 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=1001)

                    Index Cond: ((fixin_id = g.fixin_id) AND (created_on >= '2013-10-09 00:42:26.994994+01'::timestamp with time zone) AND (created_on <= '2013-11-07 23:42:26.994994+00'::timestamp with time zone))

                    Heap Fetches: 0

Planning Time: 0.316 ms

Execution Time: 5.036 ms


-- IN 2
EXPLAIN ANALYZE
SELECT created_on, sum(pain) AS sum_pain
FROM   bug_snapshots
WHERE  fixin_id IN (SELECT generate_series(2000, 3000, 1))  -- fake IN
AND    status_id IN (2, 7, 5, 3)
AND    created_on >= '2013-10-08 16:42:26.994994-0700'::timestamptz
AND    created_on <= '2013-11-07 15:42:26.994994-0800'::timestamptz
AND    pain < 999
GROUP  BY created_on
ORDER  BY created_on;


QUERY PLAN


GroupAggregate  (cost=809.52..818.79 rows=530 width=16) (actual time=4.143..4.391 rows=558 loops=1)

  Group Key: bug_snapshots.created_on

  ->  Sort  (cost=809.52..810.84 rows=530 width=12) (actual time=4.139..4.185 rows=558 loops=1)

        Sort Key: bug_snapshots.created_on

        Sort Method: quicksort  Memory: 51kB

        ->  Nested Loop  (cost=17.95..785.53 rows=530 width=12) (actual time=0.398..3.962 rows=558 loops=1)

              ->  HashAggregate  (cost=17.54..19.54 rows=200 width=4) (actual time=0.364..0.637 rows=1001 loops=1)

                    Group Key: generate_series(2000, 3000, 1)

                    Batches: 1  Memory Usage: 145kB

                    ->  ProjectSet  (cost=0.00..5.02 rows=1001 width=4) (actual time=0.002..0.094 rows=1001 loops=1)

                          ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

              ->  Index Only Scan using bug_snapshots_part_idx1 on bug_snapshots  (cost=0.42..3.82 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=1001)

                    Index Cond: ((fixin_id = (generate_series(2000, 3000, 1))) AND (created_on >= '2013-10-09 00:42:26.994994+01'::timestamp with time zone) AND (created_on <= '2013-11-07 23:42:26.994994+00'::timestamp with time zone))

                    Heap Fetches: 0

Planning Time: 0.330 ms

Execution Time: 4.457 ms


-- remove idx1!
DROP INDEX bug_snapshots_part_idx1;
-- JOIN 3: only pk index
EXPLAIN ANALYZE
SELECT created_on, sum(pain) AS sum_pain
--FROM   unnest('{11,12,33,…,5351}'::int[]) AS f(fixin_id)
FROM   generate_series(2000, 3000, 1) AS g(fixin_id)  --   -- fake unest
JOIN   bug_snapshots USING (fixin_id)
WHERE  status_id IN (2, 7, 5, 3)
AND    created_on >= '2013-10-08 16:42:26.994994-0700'::timestamptz
AND    created_on <= '2013-11-07 15:42:26.994994-0800'::timestamptz
AND    pain < 999
GROUP  BY created_on
ORDER  BY created_on;


QUERY PLAN


GroupAggregate  (cost=6468.96..6478.23 rows=530 width=16) (actual time=7.409..7.660 rows=558 loops=1)

  Group Key: bug_snapshots.created_on

  ->  Sort  (cost=6468.96..6470.28 rows=530 width=12) (actual time=7.404..7.451 rows=558 loops=1)

        Sort Key: bug_snapshots.created_on

        Sort Method: quicksort  Memory: 51kB

        ->  Nested Loop  (cost=0.42..6444.98 rows=530 width=12) (actual time=0.121..7.127 rows=558 loops=1)

              ->  Function Scan on generate_series g  (cost=0.00..10.01 rows=1001 width=4) (actual time=0.076..0.265 rows=1001 loops=1)

              ->  Index Scan using bug_snapshots_pkey on bug_snapshots  (cost=0.42..6.42 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=1001)

                    Index Cond: ((fixin_id = g.fixin_id) AND (created_on >= '2013-10-09 00:42:26.994994+01'::timestamp with time zone) AND (created_on <= '2013-11-07 23:42:26.994994+00'::timestamp with time zone))

                    Filter: ((pain < 999) AND (status_id = ANY ('{2,7,5,3}'::integer[])))

                    Rows Removed by Filter: 1

Planning Time: 0.267 ms

Execution Time: 7.723 ms


-- IN 3: only pk index
EXPLAIN ANALYZE
SELECT created_on, sum(pain) AS sum_pain
FROM   bug_snapshots
WHERE  fixin_id IN (SELECT generate_series(2000, 3000, 1))  -- fake IN
AND    status_id IN (2, 7, 5, 3)
AND    created_on >= '2013-10-08 16:42:26.994994-0700'::timestamptz
AND    created_on <= '2013-11-07 15:42:26.994994-0800'::timestamptz
AND    pain < 999
GROUP  BY created_on
ORDER  BY created_on;


QUERY PLAN


GroupAggregate  (cost=1640.02..1649.29 rows=530 width=16) (actual time=7.102..7.353 rows=558 loops=1)

  Group Key: bug_snapshots.created_on

  ->  Sort  (cost=1640.02..1641.34 rows=530 width=12) (actual time=7.095..7.143 rows=558 loops=1)

        Sort Key: bug_snapshots.created_on

        Sort Method: quicksort  Memory: 51kB

        ->  Nested Loop  (cost=17.96..1616.04 rows=530 width=12) (actual time=0.400..6.870 rows=558 loops=1)

              ->  HashAggregate  (cost=17.54..19.54 rows=200 width=4) (actual time=0.352..0.734 rows=1001 loops=1)

                    Group Key: generate_series(2000, 3000, 1)

                    Batches: 1  Memory Usage: 145kB

                    ->  ProjectSet  (cost=0.00..5.02 rows=1001 width=4) (actual time=0.003..0.093 rows=1001 loops=1)

                          ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

              ->  Index Scan using bug_snapshots_pkey on bug_snapshots  (cost=0.42..7.97 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=1001)

                    Index Cond: ((fixin_id = (generate_series(2000, 3000, 1))) AND (created_on >= '2013-10-09 00:42:26.994994+01'::timestamp with time zone) AND (created_on <= '2013-11-07 23:42:26.994994+00'::timestamp with time zone))

                    Filter: ((pain < 999) AND (status_id = ANY ('{2,7,5,3}'::integer[])))

                    Rows Removed by Filter: 1

Planning Time: 0.249 ms

Execution Time: 7.429 ms