SELECT DISTINCT is slower than expected on my table in PostgreSQL
While there is no index skip scan in Postgres yet, emulate it:
WITH RECURSIVE cte AS ( ( -- parentheses required SELECT product_id FROM tickers ORDER BY 1 LIMIT 1 ) UNION ALL SELECT l.* FROM cte c CROSS JOIN LATERAL ( SELECT product_id FROM tickers t WHERE t.product_id > c.product_id -- lateral reference ORDER BY 1 LIMIT 1 ) l )TABLE cte;With an index on (product_id) and only 40 unique product IDs in the table this should be Fast. With capital F.
The PK index on (product_id, trade_id) is good for it, too!
With only very few rows per product_id (the opposite of your data distribution), DISTINCT / DISTINCT ON would be as fast or faster.
Work to implement index skip scans is ongoing.
See: