Indexes in PostgreSQL — 1

[LevonTerGhazaryan]

I like your overall report

[erogov]

Stay tuned, more to come!

[GreedyIvan]

insert into t(a,b,c)
  select s.id, chr((32+random()*94)::integer), random() < 0.01
  from generate_series(1,100000) as s(id)
  order by random();

…
Rows are inserted into the table in a random order.

Here is an error. Rows won't be inserted in a random order which is expected.

[erogov]

No mistake here. You can verify the order by pageinspect extension or simply by selecting some rows from the table (in this case make sure the index is not used).

For example, I get the following (before creating the index):

   a   | b | c 
-------+---+---
 72673 |   | t
 70350 |   | t
 90569 |   | t
 42543 |   | t
 68301 |   | t
 67721 |   | t
 60639 |   | t
 82942 |   | t
 53841 |   | t
 97342 |   | t
(10 rows)

Stricktly speaking, the rows are inserted sequentially; it's SELECT that returns them in random order.

[GreedyIvan]

Actually we have random order only for a column. On b and c we have a preordered values with the same random() value on each column.

That is because an expression in the order by clause is calculated one time for row.

[erogov]

Yeah, but my intent was to mix rows, not columns.

[GreedyIvan]

No, no, no. Your way doesn't mix the rows.

select s.id, (random()*9)::integer, (random()*9)::integer
from generate_series(1,5) as s(id)

 a | b | c 
---+---+---
 1 | 6 | 8
 2 | 4 | 0
 3 | 2 | 5
 4 | 4 | 3
 5 | 7 | 9
(5 rows)

And now you shuffle the rows:

select s.id, (random()*9)::integer, (random()*9)::integer
from generate_series(1,5) as s(id)
order by random();

 a | b | c 
---+---+---
 2 | 1 | 1
 5 | 3 | 3
 1 | 7 | 7
 4 | 7 | 7
 5 | 9 | 9
(5 rows)

Oops.

[erogov]

Ah, I see your point now. You're right. I should've used something like

WITH t AS (SELECT ...)
SELECT * FROM t ORDER BY random();

for that. Thanks!