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 rtprices (
  scedtime timestamptz NOT NULL
, node_id  int NOT NULL
, lmp      int
, CONSTRAINT rtprices_pkey PRIMARY KEY (node_id, scedtime)  -- fixed good PK !!!
) PARTITION BY LIST (node_id);

CREATE TABLE rtprices_1 PARTITION OF rtprices FOR VALUES IN (1);
CREATE TABLE rtprices_2 PARTITION OF rtprices FOR VALUES IN (2);
CREATE TABLE rtprices_3 PARTITION OF rtprices FOR VALUES IN (3);

INSERT INTO  rtprices VALUES
  ('2021-12-01', 1)
, ('2021-12-02', 2)
, ('2021-12-03', 3)
;

CREATE TABLE node (
  node_id int PRIMARY KEY
, node text
);
INSERT INTO node VALUES
  (1, 'node1')
, (2, 'node2')
, (3, 'node3')
;

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

INSERT 0 3

CREATE TABLE

INSERT 0 3

SHOW enable_partition_pruning;

enable_partition_pruning

on

SHOW

---------- GOOD plans ----------

EXPLAIN ANALYZE
SELECT r.*
FROM   rtprices r
WHERE  node_id = 2;

QUERY PLAN

Bitmap Heap Scan on rtprices_2 r  (cost=4.22..14.76 rows=9 width=16) (actual time=0.025..0.026 rows=1 loops=1)

  Recheck Cond: (node_id = 2)

  Heap Blocks: exact=1

  ->  Bitmap Index Scan on rtprices_2_pkey  (cost=0.00..4.22 rows=9 width=0) (actual time=0.012..0.013 rows=1 loops=1)

        Index Cond: (node_id = 2)

Planning Time: 0.734 ms

Execution Time: 0.075 ms

EXPLAIN

EXPLAIN ANALYZE
SELECT r.*
FROM   rtprices r
WHERE  node_id = (SELECT node_id FROM node WHERE node = 'node2');

QUERY PLAN

Append  (cost=30.10..70.30 rows=27 width=16) (actual time=0.039..0.041 rows=1 loops=1)

  InitPlan 1 (returns $0)

    ->  Seq Scan on node  (cost=0.00..25.88 rows=6 width=4) (actual time=0.016..0.018 rows=1 loops=1)

          Filter: (node = 'node2'::text)

          Rows Removed by Filter: 2

  ->  Bitmap Heap Scan on rtprices_1 r_1  (cost=4.22..14.76 rows=9 width=16) (never executed)

        Recheck Cond: (node_id = $0)

        ->  Bitmap Index Scan on rtprices_1_pkey  (cost=0.00..4.22 rows=9 width=0) (never executed)

              Index Cond: (node_id = $0)

  ->  Bitmap Heap Scan on rtprices_2 r_2  (cost=4.22..14.76 rows=9 width=16) (actual time=0.007..0.008 rows=1 loops=1)

        Recheck Cond: (node_id = $0)

        Heap Blocks: exact=1

        ->  Bitmap Index Scan on rtprices_2_pkey  (cost=0.00..4.22 rows=9 width=0) (actual time=0.005..0.005 rows=1 loops=1)

              Index Cond: (node_id = $0)

  ->  Bitmap Heap Scan on rtprices_3 r_3  (cost=4.22..14.76 rows=9 width=16) (never executed)

        Recheck Cond: (node_id = $0)

        ->  Bitmap Index Scan on rtprices_3_pkey  (cost=0.00..4.22 rows=9 width=0) (never executed)

              Index Cond: (node_id = $0)

Planning Time: 0.777 ms

Execution Time: 0.154 ms

EXPLAIN

EXPLAIN ANALYZE
SELECT r.*
FROM   rtprices r
WHERE  node_id IN (1, 2);

QUERY PLAN

Append  (cost=8.44..37.52 rows=36 width=16) (actual time=0.010..0.014 rows=2 loops=1)

  ->  Bitmap Heap Scan on rtprices_1 r_1  (cost=8.44..18.67 rows=18 width=16) (actual time=0.009..0.010 rows=1 loops=1)

        Recheck Cond: (node_id = ANY ('{1,2}'::integer[]))

        Heap Blocks: exact=1

        ->  Bitmap Index Scan on rtprices_1_pkey  (cost=0.00..8.44 rows=18 width=0) (actual time=0.006..0.006 rows=1 loops=1)

              Index Cond: (node_id = ANY ('{1,2}'::integer[]))

  ->  Bitmap Heap Scan on rtprices_2 r_2  (cost=8.44..18.67 rows=18 width=16) (actual time=0.003..0.003 rows=1 loops=1)

        Recheck Cond: (node_id = ANY ('{1,2}'::integer[]))

        Heap Blocks: exact=1

        ->  Bitmap Index Scan on rtprices_2_pkey  (cost=0.00..8.44 rows=18 width=0) (actual time=0.002..0.002 rows=1 loops=1)

              Index Cond: (node_id = ANY ('{1,2}'::integer[]))

Planning Time: 0.462 ms

Execution Time: 0.068 ms

EXPLAIN

EXPLAIN ANALYZE
SELECT r.*
FROM   rtprices r
WHERE  node_id = 1 OR node_id = 2;

QUERY PLAN

Append  (cost=8.45..37.62 rows=36 width=16) (actual time=0.009..0.013 rows=2 loops=1)

  ->  Bitmap Heap Scan on rtprices_1 r_1  (cost=8.45..18.72 rows=18 width=16) (actual time=0.009..0.009 rows=1 loops=1)

        Recheck Cond: ((node_id = 1) OR (node_id = 2))

        Heap Blocks: exact=1

        ->  BitmapOr  (cost=8.45..8.45 rows=18 width=0) (actual time=0.006..0.006 rows=0 loops=1)

              ->  Bitmap Index Scan on rtprices_1_pkey  (cost=0.00..4.22 rows=9 width=0) (actual time=0.005..0.005 rows=1 loops=1)

                    Index Cond: (node_id = 1)

              ->  Bitmap Index Scan on rtprices_1_pkey  (cost=0.00..4.22 rows=9 width=0) (actual time=0.001..0.001 rows=0 loops=1)

                    Index Cond: (node_id = 2)

  ->  Bitmap Heap Scan on rtprices_2 r_2  (cost=8.45..18.72 rows=18 width=16) (actual time=0.002..0.003 rows=1 loops=1)

        Recheck Cond: ((node_id = 1) OR (node_id = 2))

        Heap Blocks: exact=1

        ->  BitmapOr  (cost=8.45..8.45 rows=18 width=0) (actual time=0.002..0.002 rows=0 loops=1)

              ->  Bitmap Index Scan on rtprices_2_pkey  (cost=0.00..4.22 rows=9 width=0) (actual time=0.001..0.001 rows=0 loops=1)

                    Index Cond: (node_id = 1)

              ->  Bitmap Index Scan on rtprices_2_pkey  (cost=0.00..4.22 rows=9 width=0) (actual time=0.001..0.001 rows=1 loops=1)

                    Index Cond: (node_id = 2)

Planning Time: 0.103 ms

Execution Time: 0.047 ms

EXPLAIN

EXPLAIN ANALYZE
SELECT r.*
FROM   rtprices r
WHERE  node_id = (SELECT node_id FROM node WHERE node = 'node2')
OR     node_id = (SELECT node_id FROM node WHERE node = 'node3');

QUERY PLAN

Append  (cost=60.20..108.18 rows=54 width=16) (actual time=0.038..0.047 rows=2 loops=1)

  InitPlan 1 (returns $0)

    ->  Seq Scan on node  (cost=0.00..25.88 rows=6 width=4) (actual time=0.008..0.009 rows=1 loops=1)

          Filter: (node = 'node2'::text)

          Rows Removed by Filter: 2

  InitPlan 2 (returns $1)

    ->  Seq Scan on node node_1  (cost=0.00..25.88 rows=6 width=4) (actual time=0.005..0.005 rows=1 loops=1)

          Filter: (node = 'node3'::text)

          Rows Removed by Filter: 2

  ->  Bitmap Heap Scan on rtprices_1 r_1  (cost=8.45..18.72 rows=18 width=16) (never executed)

        Recheck Cond: ((node_id = $0) OR (node_id = $1))

        ->  BitmapOr  (cost=8.45..8.45 rows=18 width=0) (never executed)

              ->  Bitmap Index Scan on rtprices_1_pkey  (cost=0.00..4.22 rows=9 width=0) (never executed)

                    Index Cond: (node_id = $0)

              ->  Bitmap Index Scan on rtprices_1_pkey  (cost=0.00..4.22 rows=9 width=0) (never executed)

                    Index Cond: (node_id = $1)

  ->  Bitmap Heap Scan on rtprices_2 r_2  (cost=8.45..18.72 rows=18 width=16) (actual time=0.018..0.019 rows=1 loops=1)

        Recheck Cond: ((node_id = $0) OR (node_id = $1))

        Heap Blocks: exact=1

        ->  BitmapOr  (cost=8.45..8.45 rows=18 width=0) (actual time=0.006..0.006 rows=0 loops=1)

              ->  Bitmap Index Scan on rtprices_2_pkey  (cost=0.00..4.22 rows=9 width=0) (actual time=0.004..0.004 rows=1 loops=1)

                    Index Cond: (node_id = $0)

              ->  Bitmap Index Scan on rtprices_2_pkey  (cost=0.00..4.22 rows=9 width=0) (actual time=0.001..0.001 rows=0 loops=1)

                    Index Cond: (node_id = $1)

  ->  Bitmap Heap Scan on rtprices_3 r_3  (cost=8.45..18.72 rows=18 width=16) (actual time=0.006..0.006 rows=1 loops=1)

        Recheck Cond: ((node_id = $0) OR (node_id = $1))

        Heap Blocks: exact=1

        ->  BitmapOr  (cost=8.45..8.45 rows=18 width=0) (actual time=0.004..0.004 rows=0 loops=1)

              ->  Bitmap Index Scan on rtprices_3_pkey  (cost=0.00..4.22 rows=9 width=0) (actual time=0.003..0.003 rows=0 loops=1)

                    Index Cond: (node_id = $0)

              ->  Bitmap Index Scan on rtprices_3_pkey  (cost=0.00..4.22 rows=9 width=0) (actual time=0.001..0.001 rows=1 loops=1)

                    Index Cond: (node_id = $1)

Planning Time: 0.220 ms

Execution Time: 0.199 ms

EXPLAIN

EXPLAIN ANALYZE
SELECT r.*
FROM   rtprices r
WHERE  node_id <> (SELECT node_id FROM node where node = 'node2');

QUERY PLAN

Append  (cost=25.88..152.87 rows=5523 width=16) (actual time=0.019..0.025 rows=2 loops=1)

  InitPlan 1 (returns $0)

    ->  Seq Scan on node  (cost=0.00..25.88 rows=6 width=4) (actual time=0.009..0.010 rows=1 loops=1)

          Filter: (node = 'node2'::text)

          Rows Removed by Filter: 2

  ->  Seq Scan on rtprices_1 r_1  (cost=0.00..33.12 rows=1841 width=16) (actual time=0.005..0.005 rows=1 loops=1)

        Filter: (node_id <> $0)

  ->  Seq Scan on rtprices_2 r_2  (cost=0.00..33.12 rows=1841 width=16) (never executed)

        Filter: (node_id <> $0)

  ->  Seq Scan on rtprices_3 r_3  (cost=0.00..33.12 rows=1841 width=16) (actual time=0.004..0.004 rows=1 loops=1)

        Filter: (node_id <> $0)

Planning Time: 0.843 ms

Execution Time: 0.070 ms

EXPLAIN

---------- still BAD plans ----------

-- using a node_id in (multiple node_id subquery) --
EXPLAIN ANALYZE
SELECT r.*
FROM   rtprices r
WHERE  node_id IN (SELECT node_id FROM node where node IN ('node2', 'node3'));

QUERY PLAN

Hash Join  (cost=26.04..153.90 rows=57 width=16) (actual time=0.156..0.164 rows=2 loops=1)

  Hash Cond: (r.node_id = node.node_id)

  ->  Append  (cost=0.00..113.25 rows=5550 width=16) (actual time=0.010..0.021 rows=3 loops=1)

        ->  Seq Scan on rtprices_1 r_1  (cost=0.00..28.50 rows=1850 width=16) (actual time=0.009..0.010 rows=1 loops=1)

        ->  Seq Scan on rtprices_2 r_2  (cost=0.00..28.50 rows=1850 width=16) (actual time=0.005..0.005 rows=1 loops=1)

        ->  Seq Scan on rtprices_3 r_3  (cost=0.00..28.50 rows=1850 width=16) (actual time=0.004..0.004 rows=1 loops=1)

  ->  Hash  (cost=25.88..25.88 rows=13 width=4) (actual time=0.034..0.035 rows=2 loops=1)

        Buckets: 1024  Batches: 1  Memory Usage: 9kB

        ->  Seq Scan on node  (cost=0.00..25.88 rows=13 width=4) (actual time=0.014..0.016 rows=2 loops=1)

              Filter: (node = ANY ('{node2,node3}'::text[]))

              Rows Removed by Filter: 1

Planning Time: 0.519 ms

Execution Time: 0.204 ms

EXPLAIN

-- combined!
EXPLAIN ANALYZE
SELECT r.*
FROM   node n
JOIN   rtprices r USING (node_id)
WHERE  n.node IN ('node2', 'node3');

QUERY PLAN

Hash Join  (cost=26.04..153.90 rows=57 width=16) (actual time=0.030..0.037 rows=2 loops=1)

  Hash Cond: (r.node_id = n.node_id)

  ->  Append  (cost=0.00..113.25 rows=5550 width=16) (actual time=0.008..0.018 rows=3 loops=1)

        ->  Seq Scan on rtprices_1 r_1  (cost=0.00..28.50 rows=1850 width=16) (actual time=0.008..0.008 rows=1 loops=1)

        ->  Seq Scan on rtprices_2 r_2  (cost=0.00..28.50 rows=1850 width=16) (actual time=0.004..0.004 rows=1 loops=1)

        ->  Seq Scan on rtprices_3 r_3  (cost=0.00..28.50 rows=1850 width=16) (actual time=0.004..0.004 rows=1 loops=1)

  ->  Hash  (cost=25.88..25.88 rows=13 width=4) (actual time=0.011..0.011 rows=2 loops=1)

        Buckets: 1024  Batches: 1  Memory Usage: 9kB

        ->  Seq Scan on node n  (cost=0.00..25.88 rows=13 width=4) (actual time=0.007..0.008 rows=2 loops=1)

              Filter: (node = ANY ('{node2,node3}'::text[]))

              Rows Removed by Filter: 1

Planning Time: 0.162 ms

Execution Time: 0.057 ms

EXPLAIN

-- favor index usage
SHOW random_page_cost;
SET random_page_cost = 1.1;

random_page_cost

4

SHOW

SET

---------- now GOOD plans ----------

-- using a node_id in (multiple node_id subquery) --
EXPLAIN ANALYZE
SELECT r.*
FROM   rtprices r
WHERE  node_id IN (SELECT node_id FROM node where node IN ('node2', 'node3'));

QUERY PLAN

Nested Loop  (cost=0.15..82.83 rows=57 width=16) (actual time=0.199..0.209 rows=2 loops=1)

  ->  Seq Scan on node  (cost=0.00..25.88 rows=13 width=4) (actual time=0.010..0.012 rows=2 loops=1)

        Filter: (node = ANY ('{node2,node3}'::text[]))

        Rows Removed by Filter: 1

  ->  Append  (cost=0.15..4.11 rows=27 width=16) (actual time=0.095..0.096 rows=1 loops=2)

        ->  Index Scan using rtprices_1_pkey on rtprices_1 r_1  (cost=0.15..1.33 rows=9 width=16) (never executed)

              Index Cond: (node_id = node.node_id)

        ->  Index Scan using rtprices_2_pkey on rtprices_2 r_2  (cost=0.15..1.33 rows=9 width=16) (actual time=0.183..0.183 rows=1 loops=1)

              Index Cond: (node_id = node.node_id)

        ->  Index Scan using rtprices_3_pkey on rtprices_3 r_3  (cost=0.15..1.33 rows=9 width=16) (actual time=0.002..0.003 rows=1 loops=1)

              Index Cond: (node_id = node.node_id)

Planning Time: 0.185 ms

Execution Time: 0.429 ms

EXPLAIN

-- using a column from a joined table
EXPLAIN ANALYZE
SELECT r.*
FROM   node n
JOIN   rtprices r USING (node_id)
WHERE  n.node = 'node2';

QUERY PLAN

Nested Loop  (cost=0.15..73.49 rows=26 width=16) (actual time=0.020..0.023 rows=1 loops=1)

  ->  Seq Scan on node n  (cost=0.00..25.88 rows=6 width=4) (actual time=0.010..0.011 rows=1 loops=1)

        Filter: (node = 'node2'::text)

        Rows Removed by Filter: 2

  ->  Append  (cost=0.15..7.66 rows=27 width=16) (actual time=0.008..0.009 rows=1 loops=1)

        ->  Index Scan using rtprices_1_pkey on rtprices_1 r_1  (cost=0.15..2.51 rows=9 width=16) (never executed)

              Index Cond: (node_id = n.node_id)

        ->  Index Scan using rtprices_2_pkey on rtprices_2 r_2  (cost=0.15..2.51 rows=9 width=16) (actual time=0.006..0.007 rows=1 loops=1)

              Index Cond: (node_id = n.node_id)

        ->  Index Scan using rtprices_3_pkey on rtprices_3 r_3  (cost=0.15..2.51 rows=9 width=16) (never executed)

              Index Cond: (node_id = n.node_id)

Planning Time: 0.246 ms

Execution Time: 0.049 ms

EXPLAIN

-- combined!
EXPLAIN ANALYZE
SELECT r.*
FROM   node n
JOIN   rtprices r USING (node_id)
WHERE  n.node IN ('node2', 'node3');

QUERY PLAN

Nested Loop  (cost=0.15..82.83 rows=57 width=16) (actual time=0.021..0.028 rows=2 loops=1)

  ->  Seq Scan on node n  (cost=0.00..25.88 rows=13 width=4) (actual time=0.011..0.012 rows=2 loops=1)

        Filter: (node = ANY ('{node2,node3}'::text[]))

        Rows Removed by Filter: 1

  ->  Append  (cost=0.15..4.11 rows=27 width=16) (actual time=0.006..0.006 rows=1 loops=2)

        ->  Index Scan using rtprices_1_pkey on rtprices_1 r_1  (cost=0.15..1.33 rows=9 width=16) (never executed)

              Index Cond: (node_id = n.node_id)

        ->  Index Scan using rtprices_2_pkey on rtprices_2 r_2  (cost=0.15..1.33 rows=9 width=16) (actual time=0.006..0.007 rows=1 loops=1)

              Index Cond: (node_id = n.node_id)

        ->  Index Scan using rtprices_3_pkey on rtprices_3 r_3  (cost=0.15..1.33 rows=9 width=16) (actual time=0.002..0.002 rows=1 loops=1)

              Index Cond: (node_id = n.node_id)

Planning Time: 0.167 ms

Execution Time: 0.054 ms

EXPLAIN