clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3610123 fiddles created (46899 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
 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
 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=9658.873..9658.892 rows=0 loops=1)
Buffers: shared hit=3032409 read=5410 dirtied=14429 written=18426
WAL: records=3003615 bytes=217593360
-> Seq Scan on demo (cost=0.00..17906.00 rows=1000000 width=22) (actual time=0.032..827.878 rows=1000000 loops=1)
Buffers: shared read=5406 written=3825
Planning:
Buffers: shared hit=21 read=1
Planning Time: 0.244 ms
Execution Time: 9659.064 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
 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
 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=12117.494..12117.508 rows=0 loops=1)
Buffers: shared hit=5024599 read=5412 dirtied=17713 written=23772
WAL: records=3006897 bytes=241214680
-> Seq Scan on demo (cost=0.00..17906.00 rows=1000000 width=22) (actual time=0.051..661.782 rows=1000000 loops=1)
Buffers: shared read=5406 written=3756
Planning:
Buffers: shared hit=18 read=1
Planning Time: 0.454 ms
Execution Time: 12117.807 ms
 hidden batch(es)