sql - 有没有比子查询更有效的方法来将 group by 的结果与表连接起来？

Question

如果我的表看起来像并且我有 start = 2017-05-24 和 end = 2017-05-27

id   deviceId   meta   createdAt
1       1        {}    2017-05-24 7:00
2       1        {}    2017-05-25 7:00
3       2        {}    2017-05-24 8:00
4       3        {}    2017-05-25 7:00
5       1        {}    2017-05-01 7:00
6       1        {}    2017-05-29 7:00
7       1        {}    2017-05-25 12:00

我想得到

   days            latest      deviceId    meta 
2017-05-24    2017-05-24 7:00    1        {}
              2017-05-24 8:00    2        {}
2017-05-25    2017-05-25 12:00   1        {}
              2017-05-25 7:00    3        {}

我当前的查询看起来像

SELECT a.meta, b.day, b."deviceId", b.latest
FROM (
    SELECT date_trunc('day', "createdAt") AS day, "deviceId", max("createdAt") AS latest
    FROM devicedata
    WHERE "createdAt" BETWEEN '2017-05-24' AND '2017-05-27'
    GROUP BY day, "deviceId"
    ORDER BY "deviceId"
) b JOIN devicedata a ON a."createdAt" = b.latest AND a."deviceId" = b."deviceId";

并且运行良好。但是:

• devicedata 表很大(超过 1.5 Go 并且增长很快)
• 请求很长(过去 5 天约 9 秒，去年约 20 秒)

有没有办法优化查询？有没有比子查询更有效的方法？

---

这是一个重现它的查询

WITH devicedata(id, deviceid, meta, createdat) as (
VALUES
    (1, 1, '{}', '2017-05-24 7:00'::timestamp),
    (2, 1, '{}', '2017-05-25 7:00'),
    (3, 2, '{}', '2017-05-24 8:00'),
    (4, 3, '{}', '2017-05-25 7:00'),
    (5, 1, '{}', '2017-05-01 7:00'),
    (6, 1, '{}', '2017-05-29 7:00'),
    (7, 1, '{}', '2017-05-25 12:00')
)
SELECT a.meta, b.day, b.deviceid, b.latest
FROM (
    SELECT date_trunc('day', createdat) AS day, deviceid, max(createdat) AS latest
    FROM devicedata
    WHERE createdat BETWEEN '2017-05-24' AND '2017-05-27'
    GROUP BY day, deviceid
    ORDER BY deviceid
) b JOIN devicedata a ON a.createdat = b.latest AND a.deviceid = b.deviceid;

---

下面是所提供的每一个方案在真实数据上的查询和执行计划

初始

查询

SELECT a.meta, b.day, b."deviceId", b.latest
FROM (
    SELECT date_trunc('day', "createdAt") AS day, "deviceId", max("createdAt") AS latest
    FROM devicedata
    WHERE "createdAt" BETWEEN '2017-05-24' AND '2017-05-27'
    GROUP BY day, "deviceId"
    ORDER BY "deviceId"
) b JOIN devicedata a ON a."createdAt" = b.latest AND a."deviceId" = b."deviceId";

执行计划

Hash Join  (cost=191507.04..246554.40 rows=70 width=946) (actual time=15135.790..17254.062 rows=42 loops=1)
  Output: a.data, a.meta, (date_trunc('day'::text, devicedata."createdAt")), devicedata."deviceId", (max(devicedata."createdAt"))
  Hash Cond: (((max(devicedata."createdAt")) = a."createdAt") AND (devicedata."deviceId" = a."deviceId"))
  ->  Sort  (cost=67241.82..67274.57 rows=13099 width=12) (actual time=4885.269..4885.298 rows=42 loops=1)
        Output: (date_trunc('day'::text, devicedata."createdAt")), devicedata."deviceId", (max(devicedata."createdAt"))
        Sort Key: devicedata."deviceId"
        Sort Method: quicksort  Memory: 28kB
        ->  HashAggregate  (cost=66182.30..66346.03 rows=13099 width=12) (actual time=4885.199..4885.233 rows=42 loops=1)
              Output: (date_trunc('day'::text, devicedata."createdAt")), devicedata."deviceId", max(devicedata."createdAt")
              Group Key: devicedata."deviceId", date_trunc('day'::text, devicedata."createdAt")
              ->  Seq Scan on public.devicedata  (cost=0.00..66076.58 rows=14096 width=12) (actual time=2081.370..4880.177 rows=14726 loops=1)
                    Output: date_trunc('day'::text, devicedata."createdAt"), devicedata."deviceId", devicedata."createdAt"
                    Filter: ((devicedata."createdAt" >= '2017-05-24 00:00:00+00'::timestamp with time zone) AND (devicedata."createdAt" <= '2017-05-27 00:00:00+00'::timestamp with time zone))
                    Rows Removed by Filter: 444176
  ->  Hash  (cost=63769.89..63769.89 rows=454289 width=938) (actual time=10250.271..10250.271 rows=458902 loops=1)
        Output: a.data, a.meta, a."createdAt", a."deviceId"
        Buckets: 8192  Batches: 128  Memory Usage: 3559kB
        ->  Seq Scan on public.devicedata a  (cost=0.00..63769.89 rows=454289 width=938) (actual time=0.760..9066.917 rows=458902 loops=1)
              Output: a.data, a.meta, a."createdAt", a."deviceId"
Planning time: 0.170 ms
Execution time: 17255.047 ms

窗口函数 ( https://stackoverflow.com/a/44256570/3580745 )

查询

WITH cte AS (
    SELECT *
    , date_trunc('day', b."createdAt") AS day
    , max(b."createdAt") OVER (partition by date_trunc('day', b."createdAt"), "deviceId") latest
    , "createdAt" = max(b."createdAt") OVER (partition by date_trunc('day', b."createdAt"),"deviceId") cond
    FROM devicedata b
    WHERE "createdAt" BETWEEN '2017-05-24' AND '2017-05-27'
)
SELECT meta,"day","deviceId", latest
FROM cte
WHERE cond
ORDER BY "deviceId", day

执行计划

Sort  (cost=74065.84..74083.47 rows=7053 width=52) (actual time=4351.730..4351.737 rows=42 loops=1)
  Output: cte.meta, cte.day, cte."deviceId", cte.latest
  Sort Key: cte."deviceId", cte.day
  Sort Method: quicksort  Memory: 65kB
  CTE cte
    ->  WindowAgg  (cost=72944.97..73332.89 rows=14106 width=950) (actual time=4302.230..4324.112 rows=14726 loops=1)
          Output: b.id, b."deviceId", b.data, b."createdAt", b."updatedAt", b.meta, (date_trunc('day'::text, b."createdAt")), max(b."createdAt") OVER (?), (b."createdAt" = max(b."createdAt") OVER (?))
          ->  Sort  (cost=72944.97..72980.24 rows=14106 width=950) (actual time=4301.828..4308.782 rows=14726 loops=1)
                Output: b."deviceId", (date_trunc('day'::text, b."createdAt")), b.id, b.data, b."createdAt", b."updatedAt", b.meta
                Sort Key: (date_trunc('day'::text, b."createdAt")), b."deviceId"
                Sort Method: external merge  Disk: 11464kB
                ->  Seq Scan on public.devicedata b  (cost=0.00..66089.29 rows=14106 width=950) (actual time=1549.320..4256.513 rows=14726 loops=1)
                      Output: b."deviceId", date_trunc('day'::text, b."createdAt"), b.id, b.data, b."createdAt", b."updatedAt", b.meta
                      Filter: ((b."createdAt" >= '2017-05-24 00:00:00+00'::timestamp with time zone) AND (b."createdAt" <= '2017-05-27 00:00:00+00'::timestamp with time zone))
                      Rows Removed by Filter: 444613
  ->  CTE Scan on cte  (cost=0.00..282.12 rows=7053 width=52) (actual time=4302.362..4351.665 rows=42 loops=1)
        Output: cte.meta, cte.day, cte."deviceId", cte.latest
        Filter: cte.cond
        Rows Removed by Filter: 14684
Planning time: 0.127 ms
Execution time: 4355.046 ms

区别于

查询

SELECT distinct on (day, "deviceId") 
       meta,
       date_trunc('day', "createdAt") AS day, 
       "deviceId", 
       "createdAt" AS latest
FROM devicedata
WHERE "createdAt" BETWEEN '2017-05-24' AND '2017-05-27'
ORDER BY "deviceId", day, "createdAt" DESC;

执行计划

Unique  (cost=71777.34..71883.14 rows=13108 width=748) (actual time=4251.585..4261.599 rows=42 loops=1)
  Output: meta, (date_trunc('day'::text, "createdAt")), "deviceId", "createdAt"
  ->  Sort  (cost=71777.34..71812.61 rows=14106 width=748) (actual time=4251.583..4258.277 rows=14726 loops=1)
        Output: meta, (date_trunc('day'::text, "createdAt")), "deviceId", "createdAt"
        Sort Key: devicedata."deviceId", (date_trunc('day'::text, devicedata."createdAt")), devicedata."createdAt" DESC
        Sort Method: external merge  Disk: 8456kB
        ->  Seq Scan on public.devicedata  (cost=0.00..66125.65 rows=14106 width=748) (actual time=1851.500..4205.084 rows=14726 loops=1)
              Output: meta, date_trunc('day'::text, "createdAt"), "deviceId", "createdAt"
              Filter: ((devicedata."createdAt" >= '2017-05-24 00:00:00+00'::timestamp with time zone) AND (devicedata."createdAt" <= '2017-05-27 00:00:00+00'::timestamp with time zone))
              Rows Removed by Filter: 444628
Planning time: 0.081 ms
Execution time: 4262.987 ms

Answer 1

使用窗口函数代替 group by 和 join:

t=# with cte as (
    SELECT *
    , date_trunc('day', b."createdAt") as day
    , "createdAt" = max(b."createdAt") over (partition by date_trunc('day', b."createdAt"),"deviceId") cond
    , max(b."createdAt") over (partition by date_trunc('day', b."createdAt"), "deviceId") latest
    FROM devicedata b
    WHERE "createdAt" BETWEEN '2017-05-24' AND '2017-05-27'
  )
  select meta,"day","deviceId", latest
  from cte
  where cond
;
 meta |         day         | deviceId |       latest
------+---------------------+----------+---------------------
  {}  | 2017-05-24 00:00:00 |        1 | 2017-05-24 07:00:00
  {}  | 2017-05-24 00:00:00 |        2 | 2017-05-24 08:00:00
  {}  | 2017-05-25 00:00:00 |        1 | 2017-05-25 12:00:00
  {}  | 2017-05-25 00:00:00 |        3 | 2017-05-25 07:00:00
(4 rows)

在您提供的示例中，窗口函数看起来更便宜:

t=# explain analyze with cte as (
    SELECT *
    , date_trunc('day', b."createdAt") as day
    , "createdAt" = max(b."createdAt") over (partition by date_trunc('day', b."createdAt"),"deviceId") cond
    , max(b."createdAt") over (partition by date_trunc('day', b."createdAt"), "deviceId") latest
    FROM devicedata b
    WHERE "createdAt" BETWEEN '2017-05-24' AND '2017-05-27'
  )
  select meta,"day","deviceId", latest
  from cte
  where cond
;
                                                                                 QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------
 CTE Scan on cte  (cost=26.25..26.35 rows=2 width=52) (actual time=0.044..0.059 rows=4 loops=1)
   Filter: cond
   Rows Removed by Filter: 1
   CTE cte
     ->  WindowAgg  (cost=26.11..26.25 rows=5 width=48) (actual time=0.040..0.051 rows=5 loops=1)
           ->  Sort  (cost=26.11..26.12 rows=5 width=48) (actual time=0.032..0.034 rows=5 loops=1)
                 Sort Key: (date_trunc('day'::text, b."createdAt")), b."deviceId"
                 Sort Method: quicksort  Memory: 25kB
                 ->  Seq Scan on devicedata b  (cost=0.00..26.05 rows=5 width=48) (actual time=0.015..0.020 rows=5 loops=1)
                       Filter: (("createdAt" >= '2017-05-24 00:00:00'::timestamp without time zone) AND ("createdAt" <= '2017-05-27 00:00:00'::times
tamp without time zone))
                       Rows Removed by Filter: 2
 Planning time: 0.126 ms
 Execution time: 0.110 ms
(13 rows)

Time: 0.774 ms

原创:

t=# explain analyze SELECT a.meta, b.day, b."deviceId", b.latest
FROM (
    SELECT date_trunc('day', "createdAt") AS day, "deviceId", max("createdAt") AS latest
    FROM devicedata
    WHERE "createdAt" BETWEEN '2017-05-24' AND '2017-05-27'
    GROUP BY day, "deviceId"
    ORDER BY "deviceId"
) b JOIN devicedata a ON a."createdAt" = b.latest AND a."deviceId" = b."deviceId";
                                                                                   QUERY P
LAN
------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
 Hash Join  (cost=26.15..54.89 rows=1 width=52) (actual time=0.040..0.045 rows=4 loops=1)
   Hash Cond: ((a."createdAt" = (max(devicedata."createdAt"))) AND (a."deviceId" = deviced
ata."deviceId"))
   ->  Seq Scan on devicedata a  (cost=0.00..20.70 rows=1070 width=44) (actual time=0.007.
.0.008 rows=7 loops=1)
   ->  Hash  (cost=26.14..26.14 rows=1 width=20) (actual time=0.027..0.027 rows=4 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Sort  (cost=26.12..26.13 rows=1 width=12) (actual time=0.024..0.025 rows=4 lo
ops=1)
               Sort Key: devicedata."deviceId"
               Sort Method: quicksort  Memory: 25kB
               ->  HashAggregate  (cost=26.10..26.11 rows=1 width=12) (actual time=0.015..
0.015 rows=4 loops=1)
                     Group Key: devicedata."deviceId", date_trunc('day'::text, devicedata.
"createdAt")
                     ->  Seq Scan on devicedata  (cost=0.00..26.06 rows=5 width=12) (actua
l time=0.005..0.008 rows=5 loops=1)
                           Filter: (("createdAt" >= '2017-05-24 00:00:00'::timestamp witho
ut time zone) AND ("createdAt" <= '2017-05-27 00:00:00'::timestamp without time zone))
                           Rows Removed by Filter: 2
 Planning time: 0.117 ms
 Execution time: 0.086 ms
(15 rows)

但是你实际数据上的结果，不一定会一样