sql - 如何在 postgres 中有效地选择具有 MIN 日期的行

Question

我需要从“最早的”(MIN(save_date))行中快速选择一个值(baz)，这些行按它们的 foo_id 分组>。以下查询返回正确的行(几乎可以，当存在重复的 save_dates 时，它可以为每个 foo_id 返回倍数)。

foos 表包含大约 55k 行，samples 表包含大约 2500 万行。

CREATE TABLE foos (
    foo_id     int,
    val        varchar(40),
    # ref_id is a FK, constraint omitted for brevity
    ref_id     int
)
CREATE TABLE samples (
    sample_id  int,
    save_date  date,
    baz        smallint,
    # foo_id is a FK, constraint omitted for brevity
    foo_id     int
)

WITH foo ( foo_id, val ) AS (
        SELECT foo_id, val FROM foos
        WHERE foos.ref_id = 1
    ORDER BY foos.val ASC
    LIMIT 25 OFFSET 0
)
SELECT foo.val, firsts.baz
FROM foo
LEFT JOIN (
    SELECT A.baz, A.foo_id
    FROM samples A
    INNER JOIN (
        SELECT foo_id, MIN( save_date ) AS save_date
        FROM samples
        GROUP BY foo_id
    ) B
    USING ( foo_id, save_date )
) firsts USING ( foo_id )

此查询目前需要 100 多秒；我希望在 ~1 秒(或更短时间!)内看到此返回。

我怎样才能将这个查询写得最优？

---

已更新；添加解释:

显然，我使用的实际查询并未使用表 foo、baz 等。

“简化”示例查询(来自上面)explain:

Hash Right Join  (cost=337.69..635.47 rows=3 width=100)
  Hash Cond: (a.foo_id = foo.foo_id)
  CTE foo
    ->  Limit  (cost=71.52..71.53 rows=3 width=102)
          ->  Sort  (cost=71.52..71.53 rows=3 width=102)
                Sort Key: foos.val
                ->  Seq Scan on foos  (cost=0.00..71.50 rows=3 width=102)
                      Filter: (ref_id = 1)
  ->  Hash Join  (cost=265.25..562.90 rows=9 width=6)
        Hash Cond: ((a.foo_id = samples.foo_id) AND (a.save_date = (min(samples.save_date))))
        ->  Seq Scan on samples a  (cost=0.00..195.00 rows=1850 width=10)
        ->  Hash  (cost=244.25..244.25 rows=200 width=8)
              ->  HashAggregate  (cost=204.25..224.25 rows=200 width=8)
                    ->  Seq Scan on samples  (cost=0.00..195.00 rows=1850 width=8)
  ->  Hash  (cost=0.60..0.60 rows=3 width=102)
        ->  CTE Scan on foo  (cost=0.00..0.60 rows=3 width=102)

Answer 1

如果我理解这个问题，你想要开窗。

WITH find_first AS (
  SELECT foo_id, baz,
    row_number()
  OVER (PARTITION BY foo_id ORDER BY foo_id, save_date) AS rnum
  FROM samples
)
SELECT foo_id, baz FROM find_first WHERE rnum = 1;

使用 row_number 而不是 rank 可以消除重复项并保证每个 foo 只有一个 baz。如果您需要了解没有 bazzes 的 foos，只需LEFT JOIN foos 表到这个查询。

有了 (foo_id, save_date) 上的索引，优化器应该足够聪明，可以只保留一个 baz 并愉快地跳过进行分组。