Postgresql – Is “TABLESAMPLE BERNOULLI(1)” not very random at all

postgresql

Whenever I need to return a random record from my tables and performance matters, instead of:

SELECT column FROM table ORDER BY random() LIMIT 1;

I always do:

SELECT column FROM table TABLESAMPLE BERNOULLI(1) LIMIT 1;

This is much faster but it seems like it's not very random? It looks like a lot of the same records get returned when using this method repeatedly. Is it just me or is this method much less random (and less useful because of that)?

Best Answer

The probability of a row to be returned from TABLESAMPLE BERNOULLI(1) is 1/100, that is, 0.01. The presence of LIMIT 1 tells Postgres to stop reading the table after the first match. This means that on average one of the first 100 rows (in whichever order they are scanned by the engine) in the table will be retrieved.

The probability of any particular row to be returned after ORDER BY random() LIMIT 1 is, on the other hand, 1/C, where C is the table cardinality. The entire table will be read and randomly ordered, then one row returned. This means that if your table has more than 100 rows, the ORDER BY random() will return a row from a wider selection (or, as you put it, will be "more random").

Some background info here.

Related Solutions

Postgresql query plan differs with limit value making the query slower for lower limits

Generally this kind of problem occurs because the query planner thinks it will find the one row you're looking within the first 1% of the table, but it doesn't. One solution for this can be to increase the stats collection on those columns, with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 5000.

However, in this particular case, I think simply changing the table_2_id index to a partial index will cause the query planner to favor it:

CREATE INDEX table_2_not_deleted ON table_1 ( table_2_id ) WHERE deleted_at IS NULL;

The query planner tends to favor partial indexes which match portions of the WHERE clause exactly. If that doesn't work, I'd try creating an index which covers everything:

CREATE INDEX problem_solver_dex
ON table_1 ( table_2_id, id )
WHERE deleted_at IS NULL

PostgreSQL Performance – Faster Alternatives to UNION ALL

For the simple case, I can only think of minor improvements to the query:

(
  SELECT DISTINCT ON (t2_id)
         t1_id, t2_id
  FROM   t0
  WHERE  t2_id IS NOT NULL
  ORDER  BY t2_id, t1_id  -- to get consistent results
)
UNION ALL
(
  SELECT DISTINCT ON (t1_id)
         t1_id, NULL      -- cheaper
  FROM   t0
  WHERE  t2_id IS NULL
  -- if you retrieve more columns, add ORDER BY, too
)

As ypercube mentioned You need to add ORDER BY with an unambiguous list of expressions to get deterministic results.
You can use the constant NULL instead of t2_id in the second leg of the query. Also relevant for below index support.
The key to performance is indexing. Try two partial indexes matching the two parts of the query:
```
CREATE INDEX t0_part1_idx ON t0 (t2_id, t1_id) WHERE t2_id IS NOT NULL;
CREATE INDEX t0_part2_idx ON t0 (t1_id)        WHERE t2_id IS NULL;
```
You may or may not want to include additional columns to allow index-only scans.

Depending on table size and data distribution, there may be faster alternatives:

Single `SELECT`

If you want to condense it into a single SELECT:

SELECT DISTINCT ON (coalesce(t2_id, t1_id), t2_id)
       t1_id, t2_id
FROM   t0
ORDER  BY coalesce(t2_id, t1_id), t2_id, t1_id;

Equivalent, except for sort order. If you want this to be fast, try a functional index:

CREATE INDEX t0_func_idx ON t0 (coalesce(t2_id, t1_id), t2_id, t1_id);

Best Answer

Related Solutions

Postgresql query plan differs with limit value making the query slower for lower limits

PostgreSQL Performance – Faster Alternatives to UNION ALL

Single SELECT

Related Question

Single `SELECT`