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 (scedtime, node_id)  -- org. bad 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=22.03..32.57 rows=9 width=16) (actual time=0.250..0.251 rows=1 loops=1)

  Recheck Cond: (node_id = 2)

  Heap Blocks: exact=1

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

        Index Cond: (node_id = 2)

Planning Time: 3.062 ms

Execution Time: 0.429 ms

EXPLAIN

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

QUERY PLAN

Append  (cost=47.90..123.73 rows=27 width=16) (actual time=0.034..0.036 rows=1 loops=1)

  InitPlan 1 (returns $0)

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

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

          Rows Removed by Filter: 2

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

        Recheck Cond: (node_id = $0)

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

              Index Cond: (node_id = $0)

  ->  Bitmap Heap Scan on rtprices_2 r_2  (cost=22.03..32.57 rows=9 width=16) (actual time=0.006..0.007 rows=1 loops=1)

        Recheck Cond: (node_id = $0)

        Heap Blocks: exact=1

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

              Index Cond: (node_id = $0)

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

        Recheck Cond: (node_id = $0)

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

              Index Cond: (node_id = $0)

Planning Time: 1.213 ms

Execution Time: 0.281 ms

EXPLAIN

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

QUERY PLAN

Append  (cost=0.00..66.43 rows=36 width=16) (actual time=0.016..0.023 rows=2 loops=1)

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

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

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

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

Planning Time: 0.813 ms

Execution Time: 0.045 ms

EXPLAIN

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

QUERY PLAN

Append  (cost=0.00..75.68 rows=36 width=16) (actual time=0.009..0.015 rows=2 loops=1)

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

        Filter: ((node_id = 1) OR (node_id = 2))

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

        Filter: ((node_id = 1) OR (node_id = 2))

Planning Time: 0.090 ms

Execution Time: 0.028 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=51.75..165.27 rows=54 width=16) (actual time=0.023..0.029 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.004..0.004 rows=1 loops=1)

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

          Rows Removed by Filter: 2

  ->  Seq Scan on rtprices_1 r_1  (cost=0.00..37.75 rows=18 width=16) (never executed)

        Filter: ((node_id = $0) OR (node_id = $1))

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

        Filter: ((node_id = $0) OR (node_id = $1))

  ->  Seq Scan on rtprices_3 r_3  (cost=0.00..37.75 rows=18 width=16) (actual time=0.005..0.005 rows=1 loops=1)

        Filter: ((node_id = $0) OR (node_id = $1))

Planning Time: 0.186 ms

Execution Time: 0.064 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.017..0.022 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.004..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.435 ms

Execution Time: 0.042 ms

EXPLAIN

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

random_page_cost

4

SHOW

SET

---------- 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.371..0.525 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.011..0.026 rows=3 loops=1)

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

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

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

  ->  Hash  (cost=25.88..25.88 rows=13 width=4) (actual time=0.065..0.066 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.032..0.035 rows=2 loops=1)

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

              Rows Removed by Filter: 1

Planning Time: 0.904 ms

Execution Time: 0.550 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

Hash Join  (cost=25.95..153.81 rows=26 width=16) (actual time=0.076..0.084 rows=1 loops=1)

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

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

        ->  Seq Scan on rtprices_1 r_1  (cost=0.00..28.50 rows=1850 width=16) (actual time=0.037..0.038 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.006 rows=1 loops=1)

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

  ->  Hash  (cost=25.88..25.88 rows=6 width=4) (actual time=0.023..0.024 rows=1 loops=1)

        Buckets: 1024  Batches: 1  Memory Usage: 9kB

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

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

              Rows Removed by Filter: 2

Planning Time: 0.176 ms

Execution Time: 0.107 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.035..0.044 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.010..0.022 rows=3 loops=1)

        ->  Seq Scan on rtprices_1 r_1  (cost=0.00..28.50 rows=1850 width=16) (actual time=0.010..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.005 rows=1 loops=1)

  ->  Hash  (cost=25.88..25.88 rows=13 width=4) (actual time=0.012..0.013 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.009..0.010 rows=2 loops=1)

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

              Rows Removed by Filter: 1

Planning Time: 0.178 ms

Execution Time: 0.066 ms

EXPLAIN