clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798808 fiddles created (41847 in the last week).

create table demo (a bigint,b bigint); insert into demo(a,b) select 1,generate_series(1,1000000);
1000000 rows affected
1000000 rows affected
 hidden batch(es)


create index demoa on demo (a) include(b); select pg_size_pretty(pg_relation_size('demo'::regclass::oid));
pg_size_pretty
42 MB
pg_size_pretty
42 MB
 hidden batch(es)


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=7974.244..7974.246 rows=0 loops=1)
Buffers: shared hit=3032409 read=5410 dirtied=14429 written=19473
WAL: records=3003615 bytes=217593360
-> 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
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
-> 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
 hidden batch(es)


drop table demo;
 hidden batch(es)


create table demo (a bigint,b bigint); insert into demo(a,b) select 1,generate_series(1,1000000);
1000000 rows affected
1000000 rows affected
 hidden batch(es)


create index demoa on demo (a,b); select pg_size_pretty(pg_relation_size('demoa'::regclass::oid));
pg_size_pretty
30 MB
pg_size_pretty
30 MB
 hidden batch(es)


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=9725.874..9725.900 rows=0 loops=1)
Buffers: shared hit=5024599 read=5412 dirtied=17713 written=24861
WAL: records=3006897 bytes=241214680
-> 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
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
-> 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
 hidden batch(es)