基于Unix时间戳按日分组的SQL查询优化咨询

表结构信息 #

CREATE TABLE public.test_table (
    id serial4 NOT NULL,
    "date" int8 NOT NULL,
    user_id int4 NOT NULL,
    device_id int4 NULL,
    CONSTRAINT test_table_date_user_id_device_id_key UNIQUE (date, user_id, device_id),
    CONSTRAINT test_table_pkey PRIMARY KEY (id)
);

ALTER TABLE public.test_table ADD CONSTRAINT test_table FOREIGN KEY (device_id) REFERENCES public.t_device(id);
ALTER TABLE public.test_table ADD CONSTRAINT test_table_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.t_user(id) ON DELETE CASCADE;

示例数据 #

需求 #

根据输入的起止日期（from和to）以及user_id，按日分组统计数据行数，同时输出每日对应的最早和最晚Unix时间戳。

当前使用的SQL #

with calendar as (
select
    d
from
    generate_series(to_timestamp(:FROM)::date,
    to_timestamp(:TO)::date,
    interval '1 day') d)

select
    c.d::date as item_date,
    count(dt.id) as item_count,
    min(dt."date") as item_min,
    max(dt."date") as item_max
from
    test_table dt 
left join
     calendar c
     on
    to_timestamp(dt.date)::date >= c.d
    and
        to_timestamp(dt.date)::date < c.d + interval '1 day'
where dt.user_id = 5
group by
    c.d
order by
    c.d;

执行计划（explain(analyze, verbose, buffers, settings)） #

Sort  (cost=199067.86..199068.36 rows=200 width=36) (actual time=10.152..10.154 rows=3 loops=1)
  Output: ((d.d)::date), (count(dt.id)), (min(dt.date)), (max(dt.date)), d.d
  Sort Key: d.d
  Sort Method: quicksort  Memory: 25kB
  Buffers: shared hit=363
  ->  HashAggregate  (cost=199057.72..199060.22 rows=200 width=36) (actual time=10.144..10.146 rows=3 loops=1)
        Output: (d.d)::date, count(dt.id), min(dt.date), max(dt.date), d.d
        Group Key: d.d
        Buffers: shared hit=363
        ->  Nested Loop  (cost=0.30..192966.61 rows=609111 width=20) (actual time=0.292..10.074 rows=432 loops=1)
              Output: d.d, dt.id, dt.date
              Join Filter: (((to_timestamp((dt.date)::double precision))::date >= d.d) AND ((to_timestamp((dt.date)::double precision))::date < (d.d + '1 day'::interval)))
              Rows Removed by Join Filter: 15174
              Buffers: shared hit=363
              ->  Function Scan on pg_catalog.generate_series d  (cost=0.01..10.01 rows=1000 width=8) (actual time=0.006..0.008 rows=3 loops=1)
                    Output: d.d
                    Function Call: generate_series((('2024-05-25 11:31:30+03'::timestamp with time zone)::date)::timestamp with time zone, (('2024-05-27 14:50:30+03'::timestamp with time zone)::date)::timestamp with time zone, '1 day'::interval)
              ->  Materialize  (cost=0.29..1100.30 rows=5482 width=12) (actual time=0.011..1.198 rows=5202 loops=3)
                    Output: dt.id, dt.date
                    Buffers: shared hit=363
                    ->  Index Scan using test_table_date_user_id_device_id_key on public.test_table dt  (cost=0.29..1072.89 rows=5482 width=12) (actual time=0.032..2.070 rows=5202 loops=1)
                          Output: dt.id, dt.date
                          Index Cond: (dt.user_id = 5)
                          Buffers: shared hit=363
Settings: effective_cache_size = '1377MB', effective_io_concurrency = '200', max_parallel_workers = '1', random_page_cost = '1.1', search_path = 'public, public, "$user"', work_mem = '524kB'
Planning Time: 0.147 ms
Execution Time: 10.264 ms

性能分析与优化方案 #

当前查询执行时间10.264ms不算慢，但存在明显优化空间，并非最优性能：

现存问题 #

1. 重复时间转换开销：SQL中多次执行to_timestamp(dt.date)::date，重复计算会浪费CPU资源。
2. 低效的连接逻辑：先取出user_id=5的所有数据再与日历表做嵌套循环连接，导致大量数据被过滤（执行计划显示15174行被过滤），无效计算占比高。
3. 索引利用不充分：现有唯一索引顺序为(date, user_id, device_id)，虽然能过滤user_id，但如果调整顺序为(user_id, date, device_id)，可以更好地支持按用户+日期的聚合操作。

优化后的SQL #

WITH calendar AS (
    SELECT d::date AS item_date
    FROM generate_series(to_timestamp(:FROM)::date, to_timestamp(:TO)::date, interval '1 day') d
),
user_daily_stats AS (
    SELECT
        to_timestamp(dt."date")::date AS item_date,
        COUNT(dt.id) AS item_count,
        MIN(dt."date") AS item_min,
        MAX(dt."date") AS item_max
    FROM public.test_table dt
    WHERE dt.user_id = 5
      -- 直接用Unix时间戳过滤范围，避免全表扫描
      AND dt."date" >= EXTRACT(EPOCH FROM to_timestamp(:FROM)::date)::bigint
      AND dt."date" < EXTRACT(EPOCH FROM (to_timestamp(:TO)::date + interval '1 day'))::bigint
    GROUP BY to_timestamp(dt."date")::date
)
SELECT
    c.item_date,
    COALESCE(u.item_count, 0) AS item_count,
    u.item_min,
    u.item_max
FROM calendar c
LEFT JOIN user_daily_stats u ON c.item_date = u.item_date
ORDER BY c.item_date;

优化点说明 #

• 提前过滤时间范围：通过Unix时间戳直接筛选目标日期区间的数据，减少扫描行数。
• 先聚合再连接：先对用户数据按日期聚合，再与日历表连接，大幅降低连接的数据量。
• 减少转换次数：每个Unix时间戳仅转换一次日期，降低计算开销。
• 补全空日期计数：用COALESCE确保无数据的日期显示0计数，符合需求。

索引优化建议 #

创建或调整索引，让数据库更高效地过滤和聚合数据：

-- 方案1：新增针对user_id和date的复合索引
CREATE INDEX idx_test_table_user_date ON public.test_table (user_id, "date");

-- 方案2：调整现有唯一索引顺序（业务允许的情况下）
DROP INDEX IF EXISTS test_table_date_user_id_device_id_key;
CREATE UNIQUE INDEX idx_test_table_user_date_device ON public.test_table (user_id, "date", device_id);

调整后的索引顺序能让数据库快速定位指定用户的所有数据，并按日期排序，提升聚合效率。

内容的提问来源于stack exchange，提问作者aim