时间戳

首页 > 解决方案 > 解释分析比 postgres 中的实际查询慢

sql - 解释分析比 postgres 中的实际查询慢

问题描述

我有以下查询

select * from activity_feed where user_id in (select following_id from user_follow where follower_id=:user_id)
union 
select * from activity_feed where project_id in (select project_id from user_project_follow where user_id=:user_id)
order by id desc limit 30

根据 postico，它在大约 14 毫秒内运行

在此处输入图像描述

但是当我做explain analyze这个查询时，计划时间是 0.5 毫秒，执行时间大约是 800 毫秒（这是我实际期望的）。这是因为没有 explain analyze返回缓存结果的查询吗？即使在这种情况下，我仍然得到不到 20 毫秒的结果。使用其他值。

哪一个更能说明我将在生产中获得的性能？我也意识到这是一个相当低效的查询，我似乎无法找出一个可以提高效率的索引。我可能不必使用union

编辑：执行计划

Limit  (cost=1380.94..1380.96 rows=10 width=148) (actual time=771.111..771.405 rows=10 loops=1)
  ->  Sort  (cost=1380.94..1385.64 rows=1881 width=148) (actual time=771.097..771.160 rows=10 loops=1)
        Sort Key: activity_feed."timestamp" DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  HashAggregate  (cost=1321.48..1340.29 rows=1881 width=148) (actual time=714.888..743.273 rows=4462 loops=1)
              Group Key: activity_feed.id, activity_feed."timestamp", activity_feed.user_id, activity_feed.verb, activity_feed.object_type, activity_feed.object_id, activity_feed.project_id, activity_feed.privacy_level, activity_feed.local_time, activity_feed.local_date
              ->  Append  (cost=5.12..1274.46 rows=1881 width=148) (actual time=0.998..682.466 rows=4487 loops=1)
                    ->  Hash Join  (cost=5.12..610.43 rows=1350 width=70) (actual time=0.982..326.089 rows=3013 loops=1)
                          Hash Cond: (activity_feed.user_id = user_follow.following_id)
                          ->  Seq Scan on activity_feed  (cost=0.00..541.15 rows=24215 width=70) (actual time=0.016..150.535 rows=24215 loops=1)
                          ->  Hash  (cost=4.78..4.78 rows=28 width=8) (actual time=0.911..0.922 rows=29 loops=1)
                                Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                ->  Index Only Scan using unique_user_follow_pair on user_follow  (cost=0.29..4.78 rows=28 width=8) (actual time=0.022..0.334 rows=29 loops=1)
                                      Index Cond: (follower_id = '17420532762804570'::bigint)
                                      Heap Fetches: 0
                    ->  Hash Join  (cost=30.50..635.81 rows=531 width=70) (actual time=0.351..301.945 rows=1474 loops=1)
                          Hash Cond: (activity_feed_1.project_id = user_project_follow.project_id)
                          ->  Seq Scan on activity_feed activity_feed_1  (cost=0.00..541.15 rows=24215 width=70) (actual time=0.027..143.896 rows=24215 loops=1)
                          ->  Hash  (cost=30.36..30.36 rows=11 width=8) (actual time=0.171..0.182 rows=11 loops=1)
                                Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                ->  Index Only Scan using idx_user_project_follow_temp on user_project_follow  (cost=0.28..30.36 rows=11 width=8) (actual time=0.020..0.102 rows=11 loops=1)
                                      Index Cond: (user_id = '17420532762804570'::bigint)
                                      Heap Fetches: 11
Planning Time: 0.571 ms
Execution Time: 771.774 ms

我在这里先向您的帮助表示感谢！

标签： sqlpostgresqlindexingquery-optimizationsql-execution-plan

解决方案

Very slow clock access like you show here (nearly 100 fold slower when TIMING defaults to ON!) usually indicates either old hardware or an old kernel IME. Not being able to trust EXPLAIN (ANALYZE) to get good data can be very frustrating if you are very particular about performance, so you should consider upgrading your hardware or your OS.

推荐阅读