How to Efficiently Select Random Rows from Large PostgreSQL Tables?

PostgreSQL random row selection method

Traditional random row selection methods are inefficient and slow when dealing with large tables containing millions or even billions of records. Two common methods are:

• Use random() to filter:

  <code class="language-sql">  select * from table where random() < 0.001;</code>

• Use order by random() and limit:

  <code class="language-sql">  select * from table order by random() limit 1000;</code>

However, due to the need for a full table scan or sorting, these methods are not the best choice for tables with a large number of rows and will cause performance bottlenecks.

Optimization methods for large tables

For the following types of tables, consider the following optimization method, which is significantly faster:

• Numeric ID columns with small or medium gaps (indexed for faster lookups)
• No or minimal write operations during selection

Query:

<code class="language-sql">WITH params AS (
  SELECT 1 AS min_id, -- 可选：自定义最小ID起始值
       5100000 AS id_span -- 近似ID范围（最大ID - 最小ID + 缓冲）
)
SELECT *
FROM (
  SELECT DISTINCT 1 + trunc(random() * p.id_span)::integer AS id
  FROM params p, generate_series(1, 1100) g
  GROUP BY 1
) r
INNER JOIN big ON r.id = big.id
LIMIT 1000;</code>

How it works:

• ID range estimate:

  • If not known exactly, query the table to estimate the minimum, maximum, and total span (max - min) of the ID column.

• Random ID generation:

  • Generate a different set of random numbers within the estimated ID range.

• Redundancy and duplication elimination:

  • Group the generated numbers to remove duplicates, reducing the possibility of selecting missing rows or already selected rows.

• Table joins and restrictions:

  • Join the random numbers with the actual table using the ID column (must be indexed). This efficient join retrieves the corresponding data for the selected row.
  • Finally, apply a limit to retrieve the required number of rows.

Why it’s fast:

• Minimal index usage:

  • The query only performs an index scan on the ID column, which is much faster than a full table scan or sort operation.

• Optimized random number generation:

  • The generated random numbers are distributed over the estimated ID range, minimizing the possibility of missing or overlapping rows.

• Redundancy and duplication elimination:

  • Grouping the generated numbers ensures that only distinct rows are selected, reducing the need for additional filtering or joining to eliminate duplicates.

Other options:

• Recursive CTE to handle gaps:

  • For tables with gaps in the ID sequence, add an additional CTE to handle these gaps.

• Function wrappers for reuse:

  • Define a function that takes limit and gap percentage as parameters, allowing easy configuration and reuse with different tables.

• Universal functions for any table:

  • Create a generic function that accepts any table with integer columns as a parameter.

• Materialize views for speed:

  • Consider creating a materialized view based on an optimized query for faster retrieval of (quasi) randomly selected rows.

• TABLE SAMPLE in PostgreSQL 9.5:

  • Leverage PostgreSQL's "TABLE SAMPLE SYSTEM" feature to implement a faster but less random row sampling method, ensuring an accurate number of rows is returned. However, keep in mind that the sample may not be completely random due to clustering effects.

The above is the detailed content of How to Efficiently Select Random Rows from Large PostgreSQL Tables?. For more information, please follow other related articles on the PHP Chinese website!