Комментарии к публикации «Indexes in PostgreSQL — 1»

13.03.2019 23:32:48 erogov

erogov — Wed, 13 Mar 2019 23:32:48 GMT

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!

13.03.2019 14:12:35 GreedyIvan

GreedyIvan — Wed, 13 Mar 2019 14:12:35 GMT

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.

13.03.2019 09:29:21 erogov

erogov — Wed, 13 Mar 2019 09:29:21 GMT

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

13.03.2019 05:59:37 GreedyIvan

GreedyIvan — Wed, 13 Mar 2019 05:59:37 GMT

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.

12.03.2019 09:19:47 erogov

erogov — Tue, 12 Mar 2019 09:19:47 GMT

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.

12.03.2019 07:53:58 GreedyIvan

GreedyIvan — Tue, 12 Mar 2019 07:53:58 GMT

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.

01.03.2019 08:53:52 erogov

erogov — Fri, 01 Mar 2019 08:53:52 GMT

Stay tuned, more to come!

28.02.2019 15:01:56 LevonTerGhazaryan

LevonTerGhazaryan — Thu, 28 Feb 2019 15:01:56 GMT

I like your overall report