sql - Postgres 忽略时间戳索引，为什么？

Question

我有以下表格:

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

我在 users.network_id 和私有(private)消息中的所有 3 列上都有索引，但是查询正在跳过索引并且需要很长时间才能运行。知道导致索引被跳过的查询有什么问题吗？

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

谢谢。

Answer 1

让我们尝试一些不同的东西。由于它的长度，我只是建议将其作为“答案”，并且您无法格式化评论。让我们将查询模块化地处理为一系列需要相交的子集。让我们看看每一个执行需要多长时间(请报告)。将您的时间戳替换为 t1 和 t2。请注意每个查询是如何建立在前一个查询之上的，从而使前一个查询成为“内联 View ”。

编辑:另外，请确认网络表中的列。

1

 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