Is it possible speed up this query by adding more indexes?

Question

I have the following SQL query

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'
   )

Below is the EXPLAIN ANALYZE results

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

Is it possible to speed up this query by adding more indexes?

Schwern · Accepted Answer · 2019-08-25 00:20:19Z

1

The query can be rewritten using joins rather than subselects. This may be faster and easier to index.

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'

Assuming all the foreign keys (property.street_id, property.city_id, etc...) are indexed this now becomes a search on street.street and zipcode.zipcode. As long as they are indexed the query should take milliseconds.

answered Aug 25, 2019 at 0:20

Schwern

167k28 gold badges225 silver badges370 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Arya Over a year ago

Thanks, that did speed things up. Now it takes less than 2 milliseconds. I converted another query from sub-queries to joins to speed it up and it got slower. This is the version that uses sub queries pastebin.com/49igKja2 execution time is 1.309 ms and this is the join version pastebin.com/vZEsVEgC and it takes 89.737 ms. Any idea why in this case using joins is slower?

Schwern Over a year ago

@Arya Possibly has to do with how full_address is indexed. I can't say more without seeing the indexes and result of explain analyze.

Arya Over a year ago

This is the EXPLAIN ANALYZE for the one that uses sub queries pastebin.com/BA5hryqe and this is the EXPLAIN ANALYZE for the one with joins pastebin.com/hZ8XGUsV

Schwern Over a year ago

@Arya That's a poor query plan for the joins. It's only using the indexes on state.id and property.full_address. It's doing full table scans of street, city, and zipcode. Maybe try analyzing the other tables?

Collectives™ on Stack Overflow

Is it possible speed up this query by adding more indexes?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related