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=8552.911..8552.913 rows=0 loops=1)

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

  WAL: records=3003615 bytes=217593360

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

        Buffers: shared read=5406 written=4152

Planning:

  Buffers: shared hit=21 read=1

Planning Time: 0.134 ms

Execution Time: 8553.009 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=10178.149..10178.151 rows=0 loops=1)

  Buffers: shared hit=5028226 read=4580 dirtied=16882 written=24280

  WAL: records=3006897 bytes=241214680

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

        Buffers: shared hit=833 read=4573 written=3555

Planning:

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

Planning Time: 0.225 ms

Execution Time: 10179.556 ms

EXPLAIN