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
改写你的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
你的sql，先把三张表union all成一张大表pp，然后再在这个pp大表里取过滤和排序，每个表一千万行，那么三个表的pp就是三千万行
而过滤和排序的本质是查找，你在三千万行数据的大表中查找的速度肯定没有在一千万行的表中查找容易。
1.把查询条件放到每个小表中，是第一次提高效率
2.每个小表过滤好后，在union all，再一次减少了最后order by的行数，再一次提高了效率
我想着你的慢会不会就是*导致？
你直接全部都是这样，难道不应该是优化的点？
--数据超千万过亿时，若非必要一般不写“*”所有字段
--若表有200个字段，需求只返回其中6个字段，建议写对应字段就可以了
--若“OFFSET 0”等阶于没有OFFSET子句，可不写
--大表order by 排序耗资源，可以分表查询后再对中间数据排序
方式一：
select * FROM
(
SELECT create_time,code,写段3,写段4,写段5,写段6
FROM 20221127表1
UNION ALL
SELECT create_time,code,写段3,写段4,写段5,写段6
FROM 20221128表2
UNION ALL
SELECT create_time,code,写段3,写段4,写段5,写段6
FROM t20221130表3
) AS t
where code = 'AAAA'
AND create_time between '2022-11-16 00:00:00' AND '2022-11-27 00:00:00'
order by create_time desc
LIMIT 10
方式二：
select * FROM
(
SELECT create_time,code,写段3,写段4,写段5,写段6
FROM 20221127表1
where code = 'AAAA'
AND create_time between '2022-11-16' AND '2022-11-27'
UNION ALL
SELECT create_time,code,写段3,写段4,写段5,写段6
FROM 20221128表2
where code = 'AAAA'
AND create_time between '2022-11-16' AND '2022-11-27'
UNION ALL
SELECT create_time,code,写段3,写段4,写段5,写段6
FROM t20221130表3
where code = 'AAAA'
AND create_time between '2022-11-16' AND '2022-11-27'
) AS t
order by create_time desc
LIMIT 10
方式三：
--尝试对方式二创建查询过程，传递构建字符串，用execute执行
--构造逻辑：
(
v_date1 varchar(20), --始create_time
v_date2 varchar(20), --止create_time
v_code varchar(50) --code
)
declare v_SQLstr text;
begin
select 'SELECT create_time,code,写段3,写段4,写段5,写段6 FROM 20221127表1 '
||' where code = '''||v_code'''
||' and create_time between '''||v_date1||'''||' and '''||v_date2||'''
||' UNION ALL'
'SELECT create_time,code,写段3,写段4,写段5,写段6 FROM 20221127表2 '
||' where code = '''||v_code'''
||' and create_time between '''||v_date1||'''||' and '''||v_date2||'''
||' UNION ALL'
'SELECT create_time,code,写段3,写段4,写段5,写段6 FROM 20221127表2 '
||' where code = '''||v_code'''
||' and create_time between '''||v_date1||'''||' and '''||v_date2||'''
|| $1 into v_SQLstr ;
execute v_SQLstr;
end