clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2555573 fiddles created (37481 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=6478.156..6478.158 rows=0 loops=1)
Buffers: shared hit=3032409 read=5410 dirtied=14429 written=20389
WAL: records=3003615 bytes=217593360
-> Seq Scan on demo (cost=0.00..17906.00 rows=1000000 width=22) (actual time=0.023..693.708 rows=1000000 loops=1)
Buffers: shared read=5406 written=4441
Planning:
Buffers: shared hit=21 read=1
Planning Time: 0.169 ms
Execution Time: 6478.248 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=7490.815..7490.816 rows=0 loops=1)
Buffers: shared hit=5024599 read=5412 dirtied=17713 written=26607
WAL: records=3006897 bytes=241214680
-> Seq Scan on demo (cost=0.00..17906.00 rows=1000000 width=22) (actual time=0.037..683.796 rows=1000000 loops=1)
Buffers: shared read=5406 written=4637
Planning:
Buffers: shared hit=18 read=1 written=1
Planning Time: 0.201 ms
Execution Time: 7491.886 ms
 hidden batch(es)