Use skip scan index to efficiently select unique permutations of columns in Postgres

Question

I've already read this excellent answer for emulating skip scan index to retrieve the distinct values for a single column. This is blazingly fast.

Now what I'm trying to do is to retrieve unique permutations (combinations) across n columns. Each column to be included has an index. There is also a combined unique index across all these columns. The table has upwards of 100'000'000 rows and all my efforts thus far have been painfully slow. Is there any way to apply the skip-scan-index approach to this problem? Any suggestions would be greatly appreciated

Answer 1

The CTE emulation of skip scan index can be generalized to n columns using Row Constructor Comparison

WITH RECURSIVE cte AS (
   (
   SELECT column1, column2, column3
   FROM   table_data
   ORDER  BY 1, 2, 3
   LIMIT  1
   )
   UNION ALL
   SELECT l.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT t.column1, t.column2, t.column3
      FROM   table_data t
      WHERE  (t.column1, t.column2, t.column3) > (c.column1, c.column2, c.column3)
      ORDER  BY 1, 2, 3
      LIMIT  1
      ) l
   )
SELECT * FROM cte;

You need to have a combined index across all these columns, in the same order of the comparison.