3 ms·

You'd use order by random() when you want to get a random sample of rows in a table. Without that, you'd end up with rows clustered according to some internal v

• acatton 3y ago
  But ORDER BY random() will scan the whole table and assign values. This is the plan I get: Limit (cost=56.39..56.41 rows=10 width=40) -> Sort (cost=56.39..59.79 rows=1360 width=40) Sort Key: (random()) -> Seq Scan on test (cost=0.00..27.00 rows=1360 width=40) One should use "FROM table TABLESAMPLE SYSTEM (size)", this is the plan: Sample Scan on test (cost=0.00..5.36 rows=136 width=32) Sampling: system ('10'::real) You can also use different distribution method than "SYSTEM", and you can make it reproducible with a seed. https://www.postgresql.org/docs/current/sql-select.html#SQL-FROM https://www.postgresql.org/docs/current/sql-select.html#SQL-... ORDER BY random() is a really un-optimized bad practice to get a random sample on PostgreSQL. If you use another SQL database, the best thing to do is to use UUIDv4 as primary key, and use "ORDER BY pk LIMIT size".
  • tmoertel 3y ago
    Note that `FROM table TABLESAMPLE SYSTEM (n/N)` is a safe substituion for `ORDER BY random() LIMIT n` only when you don't need a sample that's guaranteed to be from a uniform distribution of rows. That's because TABLESAMPLE SYSTEM samples blocks, not individual rows: > The SYSTEM method does block-level sampling with each block having the specified chance of being selected; all rows in each selected block are returned.
  • wolf550e 3y ago
    order by stored uuidv4 will get you the same rows the next time you run it. If you sample using where random() < 0.001 (or something like that) you'll get different rows each time.
  • tedunangst 3y ago
    > If you use another SQL database, the best thing to do is to use UUIDv4 as primary key, and use "ORDER BY pk LIMIT size". What if I want a different random order tomorrow?
    • __alexs 3y ago
      Then you sort descending of course /s
    • acatton 3y ago
      > > If you use another SQL database, the best thing to do is to use UUIDv4 as primary key, and use "ORDER BY pk LIMIT size". > What if I want a different random order tomorrow? If your data is large, and you want different samples. You can always always "select * from pk > gen_random_uuid() order by pk limit 10" You have a very small chance to get ffffffff-... and get zero elements. This is not perfect, I admit, but ORDER BY random() is one of the most wasteful thing. If getting random samples is very important, the best you can you is migrate to postgres and use TABLESAMPLE.
  • cryptonector 3y ago
    > But ORDER BY random() will scan the whole table and assign values. Er, no, it will assign values to the result set, which may not involve a full table scan.
    • acatton 3y ago
      Good point, but in the case of OP, without condition, it will do a full table scan. Also, any case of "WHERE filter ORDER BY random() LIMIT n" means "I want a tiny sample from a broad filter". So it might not be a full table scan, but it will still assign value to a lot of rows.
      • cryptonector 3y ago
        > Good point, but in the case of OP, without condition, it will do a full table scan. And that has nothing to do with the ORDER BY random().
        • tremon 3y ago
          Yes it does, because of LIMIT N. An unordered result set will stop after the first N matches.
          • cryptonector 3y ago
            Now that is correct.
  • hans_castorp 3y ago
    > If you use another SQL database Just a side note: TABLESAMPLE is part of the SQL standard, and e.g. Oracle supports this as well.
• Jupe 3y ago
  Agreed. (And thanks; never came across this before... ever. in 20 years) I guess some projects need to randomly select a few rows from a table? I was thinking of the wikipedia random topic, but that's only one-at-a-time, not a set of records.