SELECT DISTINCT is slower than expected on my table in PostgreSQL

by

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:

More Related Contents:

  1. Optimize GROUP BY query to retrieve latest row per user
  2. Any downsides of using data type “text” for storing strings?
  3. Way to try multiple SELECTs till a result is available?
  4. Finding similar strings with PostgreSQL quickly
  5. Best way to delete millions of rows by ID
  6. Postgres not using index when index scan is much better option
  7. Optimize groupwise maximum query
  8. PostgreSQL – fetch the rows which have the Max value for a column in each GROUP BY group
  9. How do I speed up counting rows in a PostgreSQL table?
  10. Sort by column ASC, but NULL values first?
  11. Using an Alias column in the where clause in Postgresql
  12. Grouped LIMIT in PostgreSQL: show the first N rows for each group?
  13. Optimize query with OFFSET on large table
  14. How to concatenate columns in a Postgres SELECT?
  15. How to use (install) dblink in PostgreSQL?
  16. updating table rows in postgres using subquery
  17. Find overlapping date ranges in PostgreSQL
  18. PostgreSQL multi INSERT…RETURNING with multiple columns
  19. How do you do date math that ignores the year?
  20. “Operator does not exist: integer =?” when using Postgres
  21. Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL
  22. Oracle equivalent of Postgres’ DISTINCT ON?
  23. postgresql generate sequence with no gap
  24. PostgreSQL: Case insensitive string comparison
  25. How to Optimize Queries in a Database – The Basics
  26. Grant privileges for a particular database in PostgreSQL
  27. Functions with variable number of input parameters
  28. SQL INSERT without specifying columns. What happens?
  29. How to use SQL LIKE condition with multiple values in PostgreSQL?
  30. Is there a postgres CLOSEST operator?

Leave a Comment