Postgresql数据库的调优_postgresql count会触发索引吗-优快云博客

本文链接：https://blog.youkuaiyun.com/longer201710/article/details/105576802

本文分享了在处理大量数据时，数据库查询性能优化的关键策略。包括使用时间范围查询以利用索引，避免使用函数导致索引失效，以及推荐的查询方式如连接、存在检查等。这些技巧有助于提高查询效率，减少资源消耗。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

生产数据库的数据量大了以后，各种性能瓶颈就出现了，服务稳定性就各种问题，记录下优化中遇到的各种问题

1、时间查询时，应该按范围查询，否则当数据量太大时，索引失效，走全表扫描

--没走索引
EXPLAIN ANALYSE
select * from user_order t where t.create_time < '2020-04-17 10:05:00.161';

Seq Scan on user_order t  (cost=0.00..7531.44 rows=89068 width=1196) (actual time=0.015..54.494 rows=88159 loops=1)
  Filter: (create_time < '2020-04-17 10:05:00.161'::timestamp without time zone)
  Rows Removed by Filter: 17
Planning time: 0.293 ms
Execution time: 61.134 ms

--走索引
EXPLAIN ANALYSE
select * from user_order t where 
t.create_time >'2020-04-16 10:05:00.161' and
t.create_time<'2020-04-17 10:05:00.161';

Index Scan using user_order_create_time_idx on user_order t  (cost=0.29..279.18 rows=118 width=1197) (actual time=0.047..0.230 rows=197 loops=1)
  Index Cond: ((create_time > '2020-04-16 10:05:00.161'::timestamp without time zone) AND (create_time < '2020-04-17 10:05:00.161'::timestamp without time zone))
Planning time: 0.160 ms
Execution time: 0.274 ms

2、查询某天记录，应该按范围查询，不要用函数，会导致索引失效

--没走索引
EXPLAIN ANALYSE
select * from user_order t where date(t.create_time) ='2020-04-16';

Seq Scan on user_order t  (cost=0.00..7754.12 rows=445 width=1196) (actual time=50.358..51.553 rows=183 loops=1)
  Filter: (date(create_time) = '2020-04-16'::date)
  Rows Removed by Filter: 87993
Planning time: 0.116 ms
Execution time: 51.594 ms

--走索引
EXPLAIN ANALYSE
select * from user_order t where 
t.create_time >'2020-04-16 00:00:00' and t.create_time<'2020-04-17 00:00:00';

Index Scan using user_order_create_time_idx on user_order t  (cost=0.29..237.86 rows=100 width=1196) (actual time=0.021..0.215 rows=183 loops=1)
  Index Cond: ((create_time > '2020-04-16 00:00:00'::timestamp without time zone) AND (create_time < '2020-04-17 00:00:00'::timestamp without time zone))
Planning time: 0.386 ms
Execution time: 0.326 ms