0

I'm having a table from which I want to select N random rows in PostgreSQL.

However, I don't want to select the whole table, but I want to select random from the rows that respect some constraints (e.g. Price in range, Color = "red" etc.)

I have already seen some ways to do this when id's are generated in a continuous manner. However, in this case, there will be a lot of gaps.

Is there any way of doing this without using order by random() which is to expensive?

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
silidragos
  • 161
  • 8
  • you can use ctid instead of sequential key, but it will probably take same time as `order by random()` – Vao Tsun Mar 15 '17 at 08:24

1 Answers1

1

With postgresql 9.5 you can use

TABLESAMPLE with methods BERNOULLI and SYSTEM

Hier is some good examples and explanations which is better:

How to select random rows

Compare different random methods

More examples here

First install the extension:

CREATE EXTENSION tsm_system_rows;

Try with this:

Select * from your_table tablesample   system_rows(1000)
Community
  • 1
  • 1
light souls
  • 698
  • 1
  • 8
  • 17
  • Wow, thanks! I'm gonna look into it and try to understand how it works better tomorrow. It might solve my problems. – silidragos Mar 15 '17 at 21:24
  • It works pretty good, but gets me grouped random elements, so if N is a random number I also get N+1, N+2. I think it's linked with pagination. It's faster, though. – silidragos Mar 18 '17 at 18:50
  • 1
    @silidragos I know this is years later but the SYSTEM method will return random pages of data, so they'll be grouped together, whereas the slower BERNOULLI method will return random rows – raphael Mar 22 '19 at 18:03