sql - Postgresql to_char 大大减慢了查询速度

Question

我正在尝试使用 to_char 格式化一个带有时区的时间戳列，因为我不想包含该列的区域部分，但是运行 to_char 和不运行它的查询之间的区别就像 10 秒很多时间，我对数据库没有太多经验，也许我做错了什么。

没有 to_char 时间的查询:1313 毫秒:

select distinct on ("Results"."Timestamp") "Results"."Timestamp", 
"TotalParticlesAccum", "BioAccumulated", "FlowVolume", 
"DCOffsetCh0", "DCOffsetCh1", "DCOffsetCh2", 
"LaserPower", "LaserCurrent", "LaserTemperature", 
"LaserRunHour", "FlowRate", "FlowPressure", 
"FlowTemperature", "CpuTemperature", "PwbTemperature", 
"Temperature1", "Temperature2", "Temperature3", 
"Temperature4", "TotalParticles", "Bio" 
from "Results"
Left join "SensorLog" On "Results"."SampleID" = "SensorLog"."SampleID"
where "Results"."SampleID" = id order by 1 asc;

使用 to_char 时间查询:12354 毫秒

select distinct on (to_char("Results"."Timestamp",'YYYY/MM/DD HH24:MI:SS'))     
to_char("Results"."Timestamp",'YYYY/MM/DD HH24:MI:SS'), 
"TotalParticlesAccum", "BioAccumulated", "FlowVolume", 
"DCOffsetCh0", "DCOffsetCh1", "DCOffsetCh2", 
"LaserPower", "LaserCurrent", "LaserTemperature", 
"LaserRunHour", "FlowRate", "FlowPressure", 
"FlowTemperature", "CpuTemperature", "PwbTemperature", 
"Temperature1", "Temperature2", "Temperature3", 
"Temperature4", "TotalParticles", "Bio" 
from "Results"
Left join "SensorLog" On "Results"."SampleID" = "SensorLog"."SampleID"
where "Results"."SampleID" = id order by 1 asc;

我想我知道问题是我必须 to_char 两次，但如果我没有，它会给我一个错误错误:SELECT DISTINCT ON 表达式必须匹配初始 ORDER BY 表达式

Answer 1

只需将快速查询用所需格式包装起来

select
    to_char("Timestamp",'YYYY/MM/DD HH24:MI:SS') as "Timestamp",
    "TotalParticlesAccum", "BioAccumulated", "FlowVolume", 
    "DCOffsetCh0", "DCOffsetCh1", "DCOffsetCh2", 
    "LaserPower", "LaserCurrent", "LaserTemperature", 
    "LaserRunHour", "FlowRate", "FlowPressure", 
    "FlowTemperature", "CpuTemperature", "PwbTemperature", 
    "Temperature1", "Temperature2", "Temperature3", 
    "Temperature4", "TotalParticles", "Bio"     
from (
    select distinct on ("Results"."Timestamp")
        "Results"."Timestamp", 
        "TotalParticlesAccum", "BioAccumulated", "FlowVolume", 
        "DCOffsetCh0", "DCOffsetCh1", "DCOffsetCh2", 
        "LaserPower", "LaserCurrent", "LaserTemperature", 
        "LaserRunHour", "FlowRate", "FlowPressure", 
        "FlowTemperature", "CpuTemperature", "PwbTemperature", 
        "Temperature1", "Temperature2", "Temperature3", 
        "Temperature4", "TotalParticles", "Bio" 
    from
        "Results"
        Left join
        "SensorLog" On "Results"."SampleID" = "SensorLog"."SampleID"
    where "Results"."SampleID" = id
    order by 1 asc
) s