Postgres 忽略时间戳索引，为什么? [英] Postgres is ignoring a timestamp index, why?

问题描述

我有以下表格:

users (id, network_id)
networks (id)
private_messages (id, sender_id, receiver_id, created_at)

我在 users.network_id 和私人消息中的所有 3 列上都有索引，但是查询跳过了索引并且需要很长时间才能运行.任何想法导致索引被跳过的查询中有什么问题?

I have indexes on users.network_id, and all 3 columns in private messages however the query is skipping the indexes and taking a very long time to run. Any ideas what is wrong in the query that is causing the index to be skipped?

EXPLAIN ANALYZE SELECT COUNT(*) 
FROM "networks" 
WHERE (
          networks.created_at BETWEEN ((timestamp '2013-01-01')) AND (( (timestamp '2013-01-31') + interval '-1 second'))
          AND (SELECT COUNT(*) FROM private_messages INNER JOIN users ON private_messages.receiver_id = users.id WHERE users.network_id = networks.id AND (private_messages.created_at BETWEEN ((timestamp '2013-03-01')) AND (( (timestamp '2013-03-31') + interval '-1 second'))) ) > 0)

结果:

Aggregate  (cost=722675247.10..722675247.11 rows=1 width=0) (actual time=519916.108..519916.108 rows=1 loops=1)
  ->  Seq Scan on networks  (cost=0.00..722675245.34 rows=703 width=0) (actual time=2576.205..519916.044 rows=78 loops=1)
        Filter: ((created_at >= '2013-01-01 00:00:00'::timestamp without time zone) AND (created_at <= '2013-01-30 23:59:59'::timestamp without time zone) AND ((SubPlan 1) > 0))
        SubPlan 1
          ->  Aggregate  (cost=50671.34..50671.35 rows=1 width=0) (actual time=240.359..240.359 rows=1 loops=2163)
                ->  Hash Join  (cost=10333.69..50671.27 rows=28 width=0) (actual time=233.997..240.340 rows=13 loops=2163)
                      Hash Cond: (private_messages.receiver_id = users.id)
                      ->  Bitmap Heap Scan on private_messages  (cost=10127.11..48675.15 rows=477136 width=4) (actual time=56.599..232.855 rows=473686 loops=1809)
                            Recheck Cond: ((created_at >= '2013-03-01 00:00:00'::timestamp without time zone) AND (created_at <= '2013-03-30 23:59:59'::timestamp without time zone))
                            ->  Bitmap Index Scan on index_private_messages_on_created_at  (cost=0.00..10007.83 rows=477136 width=0) (actual time=54.551..54.551 rows=473686 loops=1809)
                                  Index Cond: ((created_at >= '2013-03-01 00:00:00'::timestamp without time zone) AND (created_at <= '2013-03-30 23:59:59'::timestamp without time zone))
                      ->  Hash  (cost=205.87..205.87 rows=57 width=4) (actual time=0.218..0.218 rows=2 loops=2163)
                            Buckets: 1024  Batches: 1  Memory Usage: 0kB
                            ->  Index Scan using index_users_on_network_id on users  (cost=0.00..205.87 rows=57 width=4) (actual time=0.154..0.215 rows=2 loops=2163)
                                  Index Cond: (network_id = networks.id)
Total runtime: 519916.183 ms

谢谢.

推荐答案

让我们尝试一些不同的事情.我只是建议将此作为答案"；因为它的长度，你不能格式化评论.让我们将查询模块化地处理为一系列需要相交的子集.让我们看看执行这些操作需要多长时间(请报告).用您的时间戳替换 t1 和 t2.请注意每个查询如何建立在前一个查询之上，使前一个查询成为内联视图".

Let's try something different. I am only suggesting this as an "answer" because of its length and you cannot format a comment. Let's approach the query modularly as a series of subsets that need to get intersected. Let's see how long it takes each of these to execute (please report). Substitute your timestamps for t1 and t2. Note how each query builds upon the prior one, making the prior one an "inline view".

另外，请确认网络表中的列.

also, please confirm the columns in the Networks table.

 select PM.receiver_id from private_messages PM
 where PM.create_at between (t1 and t2)

2

 select U.id, U.network_id from users U
 join
 (
   select PM.receiver_id from private_messages PM 
   where PM.create_at between (t1 and t2)
 ) as FOO
 on U.id = FOO.receiver_id

3

select N.* from networks N
join
(
select U.id, U.network_id from users U
 join
 (
   select PM.receiver_id from private_messages PM 
   where PM.create_at between (t1 and t2)
 ) as FOO
 on U.id = FOO.receiver_id
) as BAR
on N.id = BAR.network_id

这篇关于Postgres 忽略时间戳索引，为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！