title: Index skip scan emulation to retrieve distinct product IDs and min/max for additional columns
body: <p>Here's my table schema:</p>
  <pre><code>CREATE TABLE tickers (
      product_id TEXT NOT NULL,
      trade_id INT NOT NULL,
      sequence BIGINT NOT NULL,
      time TIMESTAMPTZ NOT NULL,
      price NUMERIC NOT NULL,
      side TEXT NOT NULL,
      last_size NUMERIC NOT NULL,
      best_bid NUMERIC NOT NULL,
      best_ask NUMERIC NOT NULL,
      PRIMARY KEY (product_id, trade_id)
  );
  
  CREATE INDEX idx_tickers_product_id_time ON tickers (product_id, time);
  </code></pre>
  <p>My application subscribes to Coinbase Pro's websocket on the &quot;ticker&quot; channel and inserts a row into the tickers table whenever it receives a message.</p>
  <p>The table has over two million rows now.</p>
  <p>I learned how to use index skip scan emulation (see: <a href="https://stackoverflow.com/questions/66893968/select-distinct-is-slower-than-expected-on-my-table-in-postgresql">SELECT DISTINCT is slower than expected on my table in PostgreSQL</a>) in PostgreSQL in order to quickly retrieve distinct product_id values from this table, rather than using the slower <code>SELECT DISTINCT</code> method.</p>
  <p>I also want to retrieve min/max values for other columns. Here's what I came up with. It takes ~2.9 milliseconds over 2.25 rows.</p>
  <p>Is there a better way to accomplish this?</p>
  <pre><code>WITH product_ids AS (
      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 &gt; c.product_id  -- lateral reference
            ORDER BY 1
            LIMIT 1
            ) l
         )
      TABLE cte
  )
  SELECT
      product_id,
      (SELECT (MAX(trade_id) - MIN(trade_id) + 1) FROM tickers WHERE product_id = product_ids.product_id) AS ticker_count,
      (SELECT MIN(time) FROM tickers WHERE product_id = product_ids.product_id) AS min_time,
      (SELECT MAX(time) FROM tickers WHERE product_id = product_ids.product_id) AS max_time
  FROM product_ids
  ORDER BY ticker_count DESC
  </code></pre>