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=9365.966..9365.967 rows=0 loops=1)

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

  WAL: records=3003615 bytes=217593360

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

        Buffers: shared read=5406 written=4174

Planning:

  Buffers: shared hit=21 read=1

Planning Time: 0.209 ms

Execution Time: 9366.093 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=9354.416..9354.417 rows=0 loops=1)

  Buffers: shared hit=5024599 read=5412 dirtied=17716 written=25804

  WAL: records=3006897 bytes=241214680

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

        Buffers: shared read=5406 written=4426

Planning:

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

Planning Time: 0.239 ms

Execution Time: 9355.625 ms

EXPLAIN