时间戳

首页 > 解决方案 > 是否可以通过添加更多索引来加速此查询？

postgresql - 是否可以通过添加更多索引来加速此查询？

问题描述

我有以下 SQL 查询

EXPLAIN ANALYZE 
SELECT
   full_address,
   street_address,
   street.street,
   (
      select
         city 
      from
         city 
      where
         city.id = property.city_id
   )
   AS city,
   (
      select
         state_code 
      from
         state 
      where
         id = property.state_id
   )
   AS state_code,
   (
      select
         zipcode 
      from
         zipcode 
      where
         zipcode.id = property.zipcode_id
   )
   AS zipcode 
FROM
   property 
   INNER JOIN
      street 
      ON street.id = property.street_id 
WHERE
   street.street = 'W San Miguel Ave' 
   AND property.zipcode_id = 
   (
      SELECT
         id 
      FROM
         zipcode 
      WHERE
         zipcode = '85340'
   )

下面是EXPLAIN ANALYZE结果

Gather  (cost=1008.86..226541.68 rows=1 width=161) (actual time=59.311..21956.143 rows=184 loops=1)
  Workers Planned: 2
  Params Evaluated: $3
  Workers Launched: 2
  InitPlan 4 (returns $3)
    ->  Index Scan using zipcode_zipcode_county_id_state_id_index on zipcode zipcode_1  (cost=0.28..8.30 rows=1 width=16) (actual time=0.039..0.040 rows=1 loops=1)
          Index Cond: (zipcode = '85340'::citext)
  ->  Nested Loop  (cost=0.56..225508.35 rows=1 width=113) (actual time=7430.172..14723.451 rows=61 loops=3)
        ->  Parallel Seq Scan on street  (cost=0.00..13681.63 rows=1 width=28) (actual time=108.023..108.053 rows=1 loops=3)
              Filter: (street = 'W San Miguel Ave'::citext)
              Rows Removed by Filter: 99131
        ->  Index Scan using property_street_address_street_id_city_id_state_id_zipcode_id_c on property  (cost=0.56..211826.71 rows=1 width=117) (actual time=10983.195..21923.063 rows=92 loops=2)
              Index Cond: ((street_id = street.id) AND (zipcode_id = $3))
  SubPlan 1
    ->  Index Scan using city_id_pk on city  (cost=0.28..8.30 rows=1 width=9) (actual time=0.003..0.003 rows=1 loops=184)
          Index Cond: (id = property.city_id)
  SubPlan 2
    ->  Index Scan using state_id_pk on state  (cost=0.27..8.34 rows=1 width=3) (actual time=0.002..0.002 rows=1 loops=184)
          Index Cond: (id = property.state_id)
  SubPlan 3
    ->  Index Scan using zipcode_id_pk on zipcode  (cost=0.28..8.30 rows=1 width=6) (actual time=0.002..0.003 rows=1 loops=184)
          Index Cond: (id = property.zipcode_id)
Planning Time: 1.228 ms
Execution Time: 21956.246 ms

是否可以通过添加更多索引来加速此查询？

标签： postgresql

解决方案

可以使用连接而不是子选择来重写查询。这可能会更快更容易索引。

SELECT
   full_address,
   street_address,
   street.street,
   city.city as city,
   state.state_code as state_code,
   zipcode.zipcode as zipcode,
FROM
    property 
    INNER JOIN street  ON street.id = property.street_id 
    INNER JOIN city    ON city.id = property.city_id
    INNER JOIN state   ON state.id = property.state_id
    INNER JOIN zipcode ON zipcode.id = property.zipcode_id
WHERE
    street.street = 'W San Miguel Ave' 
    AND zipcode.zipcode = '85340'

假设所有外键（property.street_id、property.city_id 等）都已编入索引，这现在变成了对street.streetand的搜索zipcode.zipcode。只要它们被索引，查询就应该花费几毫秒。

推荐阅读