Postgres 9.6

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?.

SELECT version();

version

PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

CREATE TABLE child
(
    id INTEGER PRIMARY KEY,
    is_enabled BOOLEAN DEFAULT NULL,
    parent_id INTEGER REFERENCES child(id) DEFAULT NULL
) ;

INSERT INTO child (id, is_enabled, parent_id) VALUES (2, true, null) ;
INSERT INTO child (id, is_enabled, parent_id) VALUES (1, true, 2) ;
1 rows affected

1 rows affected

EXPLAIN ANALYZE UPDATE child SET is_enabled = FALSE WHERE id = 1; 

QUERY PLAN

Update on child  (cost=0.15..8.17 rows=1 width=15) (actual time=0.041..0.041 rows=0 loops=1)

  ->  Index Scan using child_pkey on child  (cost=0.15..8.17 rows=1 width=15) (actual time=0.006..0.006 rows=1 loops=1)

        Index Cond: (id = 1)

Planning time: 0.119 ms

Execution time: 0.062 ms

BEGIN; 
    UPDATE child SET is_enabled = FALSE WHERE id = 1; 
    EXPLAIN ANALYZE UPDATE child SET is_enabled = FALSE WHERE id = 1; 
COMMIT;
1 rows affected

QUERY PLAN

Update on child  (cost=0.15..8.17 rows=1 width=15) (actual time=0.023..0.023 rows=0 loops=1)

  ->  Index Scan using child_pkey on child  (cost=0.15..8.17 rows=1 width=15) (actual time=0.008..0.009 rows=1 loops=1)

        Index Cond: (id = 1)

Planning time: 0.024 ms

Trigger for constraint child_parent_id_fkey: time=0.134 calls=1

Execution time: 0.175 ms