Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > create table demo (a bigint,b bigint); > insert into demo(a,b) select 1,generate_series(1,1000000); > > <pre> > ✓ > 1000000 rows affected > </pre> <!-- --> > create index demoa on demo (a) include(b); > select pg_size_pretty(pg_relation_size('demo'::regclass::oid)); > > <pre> > ✓ > > | pg_size_pretty | > | :------------- | > | 42 MB | > </pre> <!-- --> > explain (analyze, wal, buffers) update demo set b=-b; --> index entry stays at same place > > <pre> > | QUERY PLAN | > | :-------------------------------------------------------------------------------------------------------------------- | > | Update on demo (cost=0.00..17906.00 rows=1000000 width=22) (actual time=7974.244..7974.246 rows=0 loops=1) | > | Buffers: shared hit=3032409 read=5410 dirtied=14429 written=19473 | > | WAL: records=3003615 bytes=217593360 | > | -&gt; Seq Scan on demo (cost=0.00..17906.00 rows=1000000 width=22) (actual time=0.033..866.848 rows=1000000 loops=1) | > | Buffers: shared read=5406 written=4144 | > | Planning: | > | Buffers: shared hit=21 read=1 | > | Planning Time: 0.218 ms | > | Execution Time: 7974.374 ms | > </pre> <!-- --> > drop table demo; > > <pre> > ✓ > </pre> <!-- --> > create table demo (a bigint,b bigint); > insert into demo(a,b) select 1,generate_series(1,1000000); > > <pre> > ✓ > 1000000 rows affected > </pre> <!-- --> > create index demoa on demo (a,b); > select pg_size_pretty(pg_relation_size('demoa'::regclass::oid)); > > <pre> > ✓ > > | pg_size_pretty | > | :------------- | > | 30 MB | > </pre> <!-- --> > explain (analyze, wal, buffers) update demo set b=-b; --> index entry moves to the other end > > > <pre> > | QUERY PLAN | > | :-------------------------------------------------------------------------------------------------------------------- | > | Update on demo (cost=0.00..17906.00 rows=1000000 width=22) (actual time=9725.874..9725.900 rows=0 loops=1) | > | Buffers: shared hit=5024599 read=5412 dirtied=17713 written=24861 | > | WAL: records=3006897 bytes=241214680 | > | -&gt; Seq Scan on demo (cost=0.00..17906.00 rows=1000000 width=22) (actual time=0.034..684.097 rows=1000000 loops=1) | > | Buffers: shared read=5406 written=3960 | > | Planning: | > | Buffers: shared hit=18 read=1 | > | Planning Time: 0.183 ms | > | Execution Time: 9726.212 ms | > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_13&fiddle=4fc59c7e6a05b14f1dd03b3d2f8859e1)*
back to fiddle