0

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

1 Answer 1

5

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.

3
  • This works - thank you! The caveat is that the unique index column-order must correspond to the column order being queried. Also, I'm trying to refine the order-by for particular columns, but I'm running into a performance dead-end, I'm guessing because of the unique index. Any ideas how this might be done? Ie, ORDER BY column2 DESC
    – hunter
    Nov 22, 2022 at 15:58
  • 1
    ORDERING of columns (ASC/DESC) should be the same of the index. But row comparison will work only if all columns are sorted in the same order, either ASC (with > operator) or DESC (with < operator). If you mix ASC and DESC then row comparison cannot be used.
    – Andrea B.
    Nov 22, 2022 at 16:13
  • 1
    A workaround would be to substitute the row comparison with an explicit set like these: WHERE (t.column1 > c.column1) OR (t.column1 = c.column1 AND t.column2 > c.column2) OR (t.column1 = c.column1 AND t.column2 = c.column2 AND t.column3 > c.column3) you can swap < for > for columns ordered in DESC order.
    – Andrea B.
    Nov 22, 2022 at 16:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.