sql - 几乎相同的 PostgreSQL 查询有 1 分钟的差异？

Question

我的 Rails 应用程序能够按 state_code 过滤记录。我注意到当我通过“CA”作为搜索词时，我几乎立即得到了结果。例如，如果我要通过“AZ”，则需要一分多钟。

我不知道为什么会这样？

下面是来自 psql 的查询解释:快一个:

 EXPLAIN ANALYZE SELECT
   accounts.id
 FROM "accounts"
 LEFT OUTER JOIN "addresses"
   ON "addresses"."addressable_id" = "accounts"."id"
   AND "addresses"."address_type" = 'mailing'
   AND "addresses"."addressable_type" = 'Account'
 WHERE "accounts"."organization_id" = 16
 AND (addresses.state_code IN ('CA'))
 ORDER BY accounts.name DESC;
                                                                       QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=4941.94..4941.94 rows=1 width=18) (actual time=74.810..74.969 rows=821 loops=1)
   Sort Key: accounts.name
   Sort Method: quicksort  Memory: 75kB
   ->  Hash Join  (cost=4.46..4941.93 rows=1 width=18) (actual time=70.044..73.148 rows=821 loops=1)
         Hash Cond: (addresses.addressable_id = accounts.id)
         ->  Seq Scan on addresses  (cost=0.00..4911.93 rows=6806 width=4) (actual time=0.027..65.547 rows=15244 loops=1)
               Filter: (((address_type)::text = 'mailing'::text) AND ((addressable_type)::text = 'Account'::text) AND ((state_code)::text = 'CA'::text))
               Rows Removed by Filter: 129688
         ->  Hash  (cost=4.45..4.45 rows=1 width=18) (actual time=2.037..2.037 rows=1775 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 87kB
               ->  Index Scan using organization_id_index on accounts  (cost=0.29..4.45 rows=1 width=18) (actual time=0.018..1.318 rows=1775 loops=1)
                     Index Cond: (organization_id = 16)
 Planning time: 0.565 ms
 Execution time: 75.224 ms
(14 rows)

慢一点:

EXPLAIN ANALYZE SELECT
  accounts.id
FROM "accounts"
LEFT OUTER JOIN "addresses"
  ON "addresses"."addressable_id" = "accounts"."id"
  AND "addresses"."address_type" = 'mailing'
  AND "addresses"."addressable_type" = 'Account'
WHERE "accounts"."organization_id" = 16
AND (addresses.state_code IN ('NV'))
ORDER BY accounts.name DESC;
                                                                       QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=4917.27..4917.27 rows=1 width=18) (actual time=97091.270..97091.277 rows=25 loops=1)
   Sort Key: accounts.name
   Sort Method: quicksort  Memory: 26kB
   ->  Nested Loop  (cost=0.29..4917.26 rows=1 width=18) (actual time=844.250..97091.083 rows=25 loops=1)
         Join Filter: (accounts.id = addresses.addressable_id)
         Rows Removed by Join Filter: 915875
         ->  Index Scan using organization_id_index on accounts  (cost=0.29..4.45 rows=1 width=18) (actual time=0.017..10.315 rows=1775 loops=1)
               Index Cond: (organization_id = 16)
         ->  Seq Scan on addresses  (cost=0.00..4911.93 rows=70 width=4) (actual time=0.110..54.521 rows=516 loops=1775)
               Filter: (((address_type)::text = 'mailing'::text) AND ((addressable_type)::text = 'Account'::text) AND ((state_code)::text = 'NV'::text))
               Rows Removed by Filter: 144416
 Planning time: 0.308 ms
 Execution time: 97091.325 ms
(13 rows)

慢的一个结果是25行，快的一个是821行，这就更让人迷惑了。

Answer 1

我通过在 psql 命令行中使用 VACUUM ANALYZE 命令解决了这个问题。