Francisco Olarte wrote
It's already been told that btrees work that way, if you find it strange read a bit about them, this is completely normal, but ...
I am just surprised by the order of magnitude in the difference though. 2 and 27 minutes that's the huge difference... I did another, simplified test, to make sure there is no duplicates and the only difference between both sets is the order:
CREATE TABLE source_sequential AS SELECT s from generate_series(1,10000000) as s;
CREATE TABLE source_random AS SELECT * from source_sequential ORDER BY random();
CREATE TABLE t_sequential (id bigint);
CREATE INDEX i_sequential ON t_sequential (id);
CREATE TABLE t_random (id bigint);
CREATE INDEX i_random ON t_random (id);
INSERT INTO t_sequential SELECT * FROM source_sequential;
102258,949 ms
INSERT INTO t_random SELECT * FROM source_random;
1657575,699 ms
View this message in context:
Re: Sequential vs. random values - number of pages in B-tree Sent from the
PostgreSQL - general mailing list archive at Nabble.com.