检索带order by 非常慢

Question

postgres 数据库分表查询，按create_time 查询非常慢，5分钟都检索不出数据。
删除order by 或者 把create_time改为 code 就很快。每个表的数据1000万行。

表：
20221127表1 20221128表2 t20221130表3
时间表 中 create_time(timestamp) 和 code(string)有索引

select *
FROM
( SELECT * FROM 20221127表1 UNION ALL SELECT * FROM 20221128表2 UNION ALL SELECT * FROM t20221130表3 ) AS PP
where
PP.create_time>= '2022-11-16 00:00:00.0'
AND PP.create_time<= '2022-11-27 00:00:00.0'
AND PP.code = 'AAAA'
**order by PP .create_time desc **
OFFSET 0
LIMIT 10

Answer 1

改写你的SQL语句,每个表先用create_time>= '2022-11-16 00:00:00.0' and create_time<= '2022-11-27 00:00:00.0' and code = 'AAAA' 然后再union all 试试看

SELECT 
    *
FROM
    (SELECT 
        *
    FROM
        csdn_mobile
    WHERE
        create_time >= '2022-11-16 00:00:00.0'
            AND create_time <= '2022-11-27 00:00:00.0'
            AND code = 'AAAA' UNION ALL SELECT 
        *
    FROM
        csdn_mobile
    WHERE
        create_time >= '2022-11-16 00:00:00.0'
            AND create_time <= '2022-11-27 00:00:00.0'
            AND code = 'AAAA' UNION ALL SELECT 
        *
    FROM
        csdn_mobile
    WHERE
        create_time >= '2022-11-16 00:00:00.0'
            AND create_time <= '2022-11-27 00:00:00.0'
            AND code = 'AAAA') AS t
ORDER BY t.create_time DESC
OFFSET 0 LIMIT 10