时间戳

首页 > 解决方案 > 有什么方法可以提高具有多个连接的查询的性能？

postgresql - 有什么方法可以提高具有多个连接的查询的性能？

问题描述

我有以下具有 6 个连接的查询

EXPLAIN ANALYZE 
SELECT
   property.id,
   full_address,
   street_address,
   street.street,
   city.city as city,
   state.state_code as state_code,
   zipcode.zipcode as zipcode,
   property_tax_history.tax AS property_tax,
   property_tax_history.land AS land_value,
   property_tax_history.improvements AS improvements_value,
   property_tax_history.year AS tax_year,
   property_sale_history.date_event AS event_date,
   property_sale_history.event AS event,
   property_sale_history.price AS event_price 
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 
   LEFT JOIN
      property_sale_history 
      ON property_sale_history.property_id = property.id 
   LEFT JOIN
      property_tax_history 
      ON property_tax_history.property_id = property.id 
WHERE
   full_address = ?;

以下是解释分析结果

Nested Loop Left Join  (cost=2.83..296.67 rows=6 width=227) (actual time=0.606..0.766 rows=28 loops=1)
  ->  Nested Loop Left Join  (cost=2.26..62.52 rows=1 width=299) (actual time=0.544..0.551 rows=4 loops=1)
        ->  Nested Loop  (cost=1.83..41.98 rows=1 width=284) (actual time=0.522..0.525 rows=1 loops=1)
              ->  Nested Loop  (cost=1.54..33.67 rows=1 width=294) (actual time=0.482..0.485 rows=1 loops=1)
                    ->  Nested Loop  (cost=1.27..25.32 rows=1 width=307) (actual time=0.439..0.441 rows=1 loops=1)
                          ->  Nested Loop  (cost=0.98..17.02 rows=1 width=314) (actual time=0.392..0.394 rows=1 loops=1)
                                ->  Index Scan using property_full_address on property  (cost=0.56..8.57 rows=1 width=318) (actual time=0.339..0.340 rows=1 loops=1)
                                      Index Cond: (full_address = '10951097-4Th-Ave-Chula-Vista-CA-91911'::citext)
                                ->  Index Scan using street_pkey on street  (cost=0.42..8.44 rows=1 width=28) (actual time=0.046..0.046 rows=1 loops=1)
                                      Index Cond: (id = property.street_id)
                          ->  Index Scan using city_id_pk on city  (cost=0.29..8.30 rows=1 width=25) (actual time=0.044..0.044 rows=1 loops=1)
                                Index Cond: (id = property.city_id)
                    ->  Index Scan using state_id_pk on state  (cost=0.28..8.32 rows=1 width=19) (actual time=0.041..0.041 rows=1 loops=1)
                          Index Cond: (id = property.state_id)
              ->  Index Scan using zipcode_id_pk on zipcode  (cost=0.29..8.30 rows=1 width=22) (actual time=0.037..0.037 rows=1 loops=1)
                    Index Cond: (id = property.zipcode_id)
        ->  Index Scan using property_sale_history_property_id on property_sale_history  (cost=0.43..20.50 rows=4 width=31) (actual time=0.019..0.021 rows=4 loops=1)
              Index Cond: (property_id = property.id)
  ->  Index Scan using property_tax_history_property_id on property_tax_history  (cost=0.56..233.54 rows=57 width=33) (actual time=0.014..0.021 rows=7 loops=4)
        Index Cond: (property_id = property.id)
Planning Time: 9.805 ms
Execution Time: 1.628 ms

有这么多连接，我可以看到每个连接都需要一些时间，而且它们加起来。无论如何我可以修改查询以加快速度吗？使用 SSD 驱动器会有帮助吗？

标签： postgresql

解决方案

问题在这里：

LEFT JOIN property_sale_history ON property_sale_history.property_id = property.id 
LEFT JOIN property_tax_history ON property_tax_history.property_id = property.id

每个表都有独立的连接，两个连接的列都在结果中。

这会产生与返回的行数有关的 n 2问题。例如，如果给定属性有 3 个销售行和 4 个税行，则结果中该属性将有 12 行。这真的会损害性能，并且没有办法“修复”性能问题，而且这样的查询无论如何也没有意义。

您应该使用两个单独的查询 - 一个用于销售历史记录，一个用于税收历史记录。

您可以考虑为这两个查询的公共部分创建一个视图。

——</p>

ps 在新行上放宽，并在 FROM 之后证明一切都将有助于提高可读性。

推荐阅读