EXPLAIN EXTENDED

In one of the previous articles:

Advanced row sampling

, I described how to select a certain number of records for each group in a MySQL table.

This is trivial in SQL Server and Oracle, since an analytic function ROW_NUMBER() can be used to do this.

Now, I'll describe how to do it in PostgreSQL.

We are assuming that the records from the table should be grouped by a field called grouper, and for each grouper, N first records should be selected, ordered first by ts, then by id.

Let's create a sample table:

CREATE TABLE t_limiter (
id INT NOT NULL PRIMARY KEY,
grouper INT NOT NULL,
ts TIMESTAMP NOT NULL,
value VARCHAR(50) NOT NULL
);

CREATE INDEX ix_limiter_grouper_ts_id ON t_limiter (grouper, ts, id);

BEGIN TRANSACTION;

SELECT  SETSEED(0.20090429);

INSERT
INTO    t_limiter (id, grouper, ts, value)
SELECT  s, s % 10,
NOW()::DATE - (RANDOM() * 1000000 || ' SECOND')::INTERVAL,
'Value ' || s
FROM    generate_series(1, 1000000) s;

COMMIT;

ANALYZE t_limiter;

A quick reminder of an algorithm to select a number of rows from a table.

1. Select DISTINCT grouper's from the table
2. For each grouper, select n'th row from the table, ordered by (ts, id). This will be used as a condition in a subsequent JOIN
3. JOIN the table with the grouper's rowset selected on step 1, using the values selected on step 2 as an upper bound for the JOIN condition

PostgreSQL provides a convenient way to select whole row from a table as a single column.

This allows us to overcome the 1 column, 1 row limitation of a subquery in SELECT list, that required us to use two subqueries for the same solution in MySQL:

SELECT  l.*
FROM    (
SELECT  grouper,
(
SELECT  li
FROM    t_limiter li
WHERE   li.grouper = dl.grouper
ORDER BY
grouper, ts, id
OFFSET  2 LIMIT 1
) AS mid
FROM    (
SELECT  grouper
FROM    t_limiter lo
GROUP BY
grouper
) dl
) dlo
JOIN    t_limiter l
ON      l.grouper = dlo.grouper
AND ROW(l.ts, l.id) < ROW(COALESCE((mid).ts, 'Infinity'::TIMESTAMP), (mid).id)

Nested Loop  (cost=19854.84..741196.47 rows=333329 width=28)
  ->  HashAggregate  (cost=19852.83..19852.92 rows=10 width=4)
        ->  Seq Scan on t_limiter lo  (cost=0.00..17352.86 rows=999986 width=4)
  ->  Index Scan using ix_limiter_grouper_ts_id on t_limiter l  (cost=2.02..4337.93 rows=33333 width=28)
        Index Cond: ((l.grouper = lo.grouper) AND (ROW(l.ts, l.id) < ROW(COALESCE(((subplan)).ts, 'infinity'::timestamp without time zone), ((subplan)).id)))
        SubPlan
          ->  Limit  (cost=0.67..1.01 rows=1 width=48)
                ->  Index Scan using ix_limiter_grouper_ts_id on t_limiter li  (cost=0.00..33606.54 rows=99999 width=48)
                      Index Cond: (grouper = $0)
          ->  Limit  (cost=0.67..1.01 rows=1 width=48)
                ->  Index Scan using ix_limiter_grouper_ts_id on t_limiter li  (cost=0.00..33606.54 rows=99999 width=48)
                      Index Cond: (grouper = $0)

Note the major differences between MySQL and PostgreSQL solutions:

As a result, this PostgreSQL query looks much nicer than MySQL's one, but performs more poorly, due to PostgreSQL's inability to use an INDEX GROUP BY employed by MySQL to select distinct values.