我有一个表,其中有一个日期时间字段“updated_at”。我很多查询都会使用范围查询来查询此字段,例如更新日期大于某个特定日期的行。
我已经为updated_at添加了索引,但即使我对返回行数设置限制,我的大部分查询仍然非常慢。
除了添加索引外,我还能做什么来优化查询日期时间字段的查询?
我有一个表,其中有一个日期时间字段“updated_at”。我很多查询都会使用范围查询来查询此字段,例如更新日期大于某个特定日期的行。
我已经为updated_at添加了索引,但即使我对返回行数设置限制,我的大部分查询仍然非常慢。
除了添加索引外,我还能做什么来优化查询日期时间字段的查询?
updated_at > somedate
)使用索引。但是,在许多情况下,日期时间列不会超过"现在",因此您可以通过使用between
将条件转换为一个范围来保留> somedate
的语义,例如:
where updated_at between somedate and current_timestamp
BETWEEN
谓词更有可能导致优化器选择使用索引。
如果这种方法提高了查询的性能,请发布结果。
>
)操作,无需使用between操作符。请参考这里的示例http://sqlfiddle.com/#!12/e3142/3 。就像通常使用索引一样,一切都取决于使用索引的成本是否比其他操作更低。 - user330315between
大大提高了我的性能。 - Paul Odeon>
,我们得到了Seq Scan on wiki_helpfulvote (cost=0.00..1811033.95 rows=18614372 width=635)
。通过使用between X and current_timestamp
,我们得到了Index Scan using wiki_helpfulvote_created_06b8907e on wiki_helpfulvote (cost=0.57..1008752.89 rows=279216 width=6
。这似乎是一个明显的优势。也许PostgreSQL使用current_timestamp
来意识到它不必担心未来的写入,并且不需要表锁定。 - undefined针对任何查询,使用索引取决于使用该索引的成本与顺序扫描的成本之间的比较
开发人员通常认为,由于存在索引,查询应该运行得更快,如果查询运行缓慢,则索引是解决方案。但是,随着结果中元组数量的增加,使用索引的成本可能会增加。
您正在使用postgres。Postgres不支持围绕给定属性进行聚类。这意味着当postgres面临区间查询(如att>a和att<b)时,需要计算结果中元组数量的估计值(确保频繁清理数据库)以及使用索引与执行顺序扫描相比的成本。然后它将决定使用什么方法。
您可以通过运行以下命令来检查此决策:
EXPLAIN ANALYZE <query>;
我在一张近100万行的表中遇到了类似的情况。
因此,我在visited_at(日期时间字段)上创建了一个B树索引,并尝试查询所有行:
最初的回答:
explain analyze select mes,count(usuario) as usuarios
from (
SELECT distinct coalesce(usuario, ip) as usuario, (extract(year from visited_at), extract(month from visited_at)) AS mes
FROM pageview
) as usuarios
group by 1
order by 1
我理解的是:
GroupAggregate (cost=445468.78..451913.54 rows=200 width=64) (actual time=31027.876..31609.754 rows=8 loops=1)
-> Sort (cost=445468.78..447616.37 rows=859035 width=64) (actual time=31013.501..31439.350 rows=358514 loops=1)
Sort Key: usuarios.mes
Sort Method: external merge Disk: 24000kB
-> Subquery Scan on usuarios (cost=247740.16..263906.75 rows=859035 width=64) (actual time=23121.403..28200.175 rows=358514 loops=1)
-> Unique (cost=247740.16..255316.40 rows=859035 width=48) (actual time=23121.400..28129.538 rows=358514 loops=1)
-> Sort (cost=247740.16..250265.57 rows=1010166 width=48) (actual time=23121.399..27559.241 rows=1010702 loops=1)
Sort Key: (COALESCE(pageview.usuario, (pageview.ip)::text)), (ROW(date_part('year'::text, pageview.visited_at), date_part('month'::text, pageview.visited_at)))
Sort Method: external merge Disk: 66944kB
-> Seq Scan on pageview (cost=0.00..84842.49 rows=1010166 width=48) (actual time=0.012..1909.324 rows=1010702 loops=1)
Total runtime: 31632.012 ms
explain analyze select mes,count(usuario) as usuarios
from (
SELECT distinct coalesce(usuario, ip) as usuario, (extract(year from visited_at), extract(month from visited_at)) AS mes
FROM pageview
where visited_at > current_date - 31
) as usuarios
group by 1
order by 1
and got
-> Sort (cost=164735.62..165310.93 rows=230125 width=64) (actual time=9532.343..9602.743 rows=90871 loops=1)
Sort Key: usuarios.mes
Sort Method: external merge Disk: 5872kB
-> Subquery Scan on usuarios (cost=122598.79..126929.62 rows=230125 width=64) (actual time=7251.344..9178.901 rows=90871 loops=1)
-> Unique (cost=122598.79..124628.37 rows=230125 width=48) (actual time=7251.343..9157.837 rows=90871 loops=1)
-> Sort (cost=122598.79..123275.32 rows=270610 width=48) (actual time=7251.341..8932.541 rows=294915 loops=1)
Sort Key: (COALESCE(pageview.usuario, (pageview.ip)::text)), (ROW(date_part('year'::text, pageview.visited_at), date_part('month'::text, pageview.visited_at)))
Sort Method: external merge Disk: 18864kB
-> Bitmap Heap Scan on pageview (cost=5073.60..81528.85 rows=270610 width=48) (actual time=111.950..1877.603 rows=294915 loops=1)
Recheck Cond: (visited_at > (('now'::cstring)::date - 31))
Rows Removed by Index Recheck: 338268
-> Bitmap Index Scan on visited_at_index (cost=0.00..5005.94 rows=270610 width=0) (actual time=109.874..109.874 rows=294915 loops=1)
Index Cond: (visited_at > (('now'::cstring)::date - 31))
Total runtime: 9687.460 ms
我对将datetime转换为date进行了小幅改进(visited_at :: date)。
最初的回答
explain analyze select mes,count(usuario) as usuarios
from (
SELECT distinct coalesce(usuario, ip) as usuario, (extract(year from visited_at::date), extract(month from visited_at::date)) AS mes
FROM pageview
where visited_at::date > current_date - 31
) as usuarios
group by 1
order by 1
and got
GroupAggregate (cost=201976.97..204126.56 rows=200 width=64) (actual time=9040.196..9102.098 rows=2 loops=1)
-> Sort (cost=201976.97..202692.83 rows=286345 width=64) (actual time=9035.624..9058.457 rows=88356 loops=1)
Sort Key: usuarios.mes
Sort Method: external merge Disk: 5704kB
-> Subquery Scan on usuarios (cost=149102.66..154491.53 rows=286345 width=64) (actual time=7511.231..8840.270 rows=88356 loops=1)
-> Unique (cost=149102.66..151628.08 rows=286345 width=48) (actual time=7511.229..8823.647 rows=88356 loops=1)
-> Sort (cost=149102.66..149944.47 rows=336722 width=48) (actual time=7511.227..8666.667 rows=287614 loops=1)
Sort Key: (COALESCE(pageview.usuario, (pageview.ip)::text)), (ROW(date_part('year'::text, ((pageview.visited_at)::date)::timestamp without time zone), date_part('month'::text, ((pageview.visited_at)::date)::timestamp without time zone)))
Sort Method: external merge Disk: 18408kB
-> Seq Scan on pageview (cost=0.00..97469.57 rows=336722 width=48) (actual time=0.018..1946.139 rows=287614 loops=1)
Filter: ((visited_at)::date > (('now'::cstring)::date - 31))
Rows Removed by Filter: 722937
Total runtime: 9108.644 ms
insert from month_users select mes,count(usuario) as usuarios
from (
SELECT distinct coalesce(usuario, ip) as usuario, (extract(year from visited_at), extract(month from visited_at)) AS mes
FROM pageview
) as usuarios
group by 1
order by 1
select * from month_users
results:
Seq Scan on usuarios_mes (cost=0.00..21.30 rows=1130 width=42) (actual time=0.302..0.304 rows=8 loops=1)
Total runtime: 0.336 ms
现在有一个可接受的结果!
但是最终的解决方案仍然需要考虑如何定期更新表格结果。
Original Answer翻译成"最初的回答"