A recursive CTE technique for fast retrieval of unique values from a specific column in a PostgreSQL table. By leveraging a B-Tree index for index-only scans, it performs significantly faster and lighter than a regular SELECT DISTINCT.

get_foo_ids_on_hoges_by_recursive_cte.sql

WITH RECURSIVE foo_ids AS (
  (SELECT foo_id FROM hoges ORDER BY foo_id LIMIT 1)
  UNION ALL
  SELECT (SELECT foo_id FROM hoges WHERE foo_id > f.foo_id ORDER BY foo_id LIMIT 1)
  FROM foo_ids f
  WHERE f.foo_id IS NOT NULL
)
SELECT foo_id FROM foo_ids WHERE foo_id IS NOT NULL