优化 BETWEEN 日期语句 [英] Optimize BETWEEN date statement
问题描述
我需要帮助优化使用 BETWEEN
子句和 timestamp
字段的 PostgreSQL 查询.
I need help in optimize a PostgreSQL query which uses the BETWEEN
clause with a timestamp
field.
我有两张桌子:
ONE(int id_one(PK), datetime cut_time, int f1 . . .)
包含大约 3394 行
containing about 3394 rows
TWO(int id_two(PK), int id_one(FK), int f2 . . .)
包含大约 4000000 行
containing about 4000000 rows
在主键 id_one
和 id_two
、FK id_one
和 cut_time
上都有 btree 索引.
There are btree indexes on both PKs id_one
and id_two
, on the FK id_one
and cut_time
.
我想执行如下查询:
select o.id_one, Date(o.cut_time), o.f1, t.f2
from one o
inner join two t ON (o.id_one = t.id_one)
where o.cut_time between '2013-01-01' and '2013-01-31';
此查询在大约 7 秒内检索了大约 1.700.000 行.
This query retrieves about 1.700.000 rows in about 7 seconds.
下面是explain analysis报告:
Below the explain analyze report is reported:
"Merge Join (cost=20000000003.53..20000197562.38 rows=1680916 width=24) (actual time=0.017..741.718 rows=1692345 loops=1)"
" Merge Cond: (c.coilid = hf.coilid)"
" -> Index Scan using pk_coils on coils c (cost=10000000000.00..10000000382.13 rows=1420 width=16) (actual time=0.008..4.539 rows=1404 loops=1)"
" Filter: ((cut_time >= '2013-01-01 00:00:00'::timestamp without time zone) AND (cut_time <= '2013-01-31 00:00:00'::timestamp without time zone))"
" Rows Removed by Filter: 1990"
" -> Index Scan using idx_fk_lf_data on hf_data hf (cost=10000000000.00..10000166145.90 rows=4017625 width=16) (actual time=0.003..392.535 rows=1963386 loops=1)"
"Total runtime: 768.473 ms"
时间戳列上的索引未使用.如何优化这个查询?
The index on the timestamp column isn't used. How to optimize this query?
推荐答案
查询在不到一秒的时间内执行.其他 6 秒以上用于服务器和客户端之间的流量.
The query executes in less than one second. The other 6+ seconds are spent on traffic between server and client.
这篇关于优化 BETWEEN 日期语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!