我有一个大的表（location_history），里面有uid和geohash，它们都是主键和索引。
Indexes:
"location_history_pkey" PRIMARY KEY, btree (uid, geohash)
"ix_location_history_geohash" btree (geohash)
"ix_location_history_uid" btree (uid)字符串
但是当我通过以下约束查询数据时，它运行得真的很慢（169秒！）甚至这两列已经被索引：
EXPLAIN (analyze, verbose, buffers) SELECT * FROM location_history WHERE uid = 123 AND geohash >= 'hzzzzzzzzzz';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using location_history_pkey on public.location_history (cost=0.71..84735.92 rows=22438 width=29) (actual time=1.434..114713.751 rows=189319 loops=1)
Output: created_at, updated_at, uid, geohash, is_from_zenly
Index Cond: ((location_history.uid = 123) AND ((location_history.geohash)::text >= 'hzzzzzzzzzz'::text))
Buffers: shared hit=5822 read=179864 dirtied=132 written=19338
I/O Timings: read=111433.601 write=2564.930
Query Identifier: -7646491345250917333
Planning Time: 0.103 ms
Execution Time: 114746.908 ms
(8 rows)型
下面是pgstattuple()的结果：
SELECT * FROM pgstattuple(416708);
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
--------------+-------------+--------------+---------------+------------------+----------------+--------------------+------------+--------------
812873588736 | 15570800464 | 685124233392 | 84.28 | 1506 | 66264 | 0 | 394834592 | 0.05
(1 row)型
膨胀率约为34%：
SELECT 100-(pgstatindex('location_history_pkey')).avg_leaf_density;
?column?
--------------------
34.260000000000005
(1 row)型
你能分享一些加快查询速度的技巧吗？- 谢谢-谢谢
1条答案
按热度按时间xuo3flqw1#
如果只进行索引扫描，可以得到很大的改进，可以在索引中包含查询所需的所有列，显然是
(uid, geohash, is_from_zenly, created_at, updated_at)。在这种情况下，它只需要访问索引页，而不需要访问（随机分散的）表页，只要表被很好地清空。您可以通过以下操作获得“覆盖”索引，而无需部分复制主键索引：字符串
当然，您需要首先删除当前的主键，因此这将导致一些停机时间。您可以只创建一个新的索引，并使用冗余索引。
当然，如果真实的的查询集可能包含比示例中显示的更多的列，那么这可能不是一个非常完整的解决方案。