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 demo (a bigint,b bigint);
insert into demo(a,b) select 1,generate_series(1,1000000);

CREATE TABLE

INSERT 0 1000000

create index demoa on demo (a) include(b);
select pg_size_pretty(pg_relation_size('demo'::regclass::oid));

CREATE INDEX

pg_size_pretty

42 MB

SELECT 1

explain (analyze, wal, buffers) update demo set b=-b;      --> index entry stays at same place

QUERY PLAN

Update on demo  (cost=0.00..17906.00 rows=1000000 width=22) (actual time=6091.127..6091.128 rows=0 loops=1)

  Buffers: shared hit=3032409 read=5410 dirtied=14429 written=20655

  WAL: records=3003615 bytes=217593360

  ->  Seq Scan on demo  (cost=0.00..17906.00 rows=1000000 width=22) (actual time=0.032..393.352 rows=1000000 loops=1)

        Buffers: shared read=5406 written=4550

Planning:

  Buffers: shared hit=21 read=1

Planning Time: 0.270 ms

Execution Time: 6091.246 ms

EXPLAIN

drop table demo;

DROP TABLE

create table demo (a bigint,b bigint);
insert into demo(a,b) select 1,generate_series(1,1000000);

CREATE TABLE

INSERT 0 1000000

create index demoa on demo (a,b);
select pg_size_pretty(pg_relation_size('demoa'::regclass::oid));

CREATE INDEX

pg_size_pretty

30 MB

SELECT 1

explain (analyze, wal, buffers) update demo set b=-b;        --> index entry moves to the other end

QUERY PLAN

Update on demo  (cost=0.00..17906.00 rows=1000000 width=22) (actual time=7940.406..7940.407 rows=0 loops=1)

  Buffers: shared hit=5024598 read=5413 dirtied=17714 written=26421

  WAL: records=3006897 bytes=241214680

  ->  Seq Scan on demo  (cost=0.00..17906.00 rows=1000000 width=22) (actual time=0.028..468.636 rows=1000000 loops=1)

        Buffers: shared read=5406 written=4655

Planning:

  Buffers: shared hit=18 read=1 written=1

Planning Time: 0.224 ms

Execution Time: 7941.536 ms

EXPLAIN