优化Postgresql查询

Question

我在PostgreSQL 9.1中有两个表 - flight_2012_09_12包含大约50万行数据，position_2012_09_12包含大约550万行数据。我正在运行一个简单的联接查询，但是它需要很长时间才能完成，尽管这些表不小，但我相信可以在执行中获得一些重大的收益。

查询语句如下：

SELECT f.departure, f.arrival, 
       p.callsign, p.flightkey, p.time, p.lat, p.lon, p.altitude_ft, p.speed 
FROM position_2012_09_12 AS p 
JOIN flight_2012_09_12 AS f 
     ON p.flightkey = f.flightkey 
WHERE p.lon < 0 
      AND p.time BETWEEN '2012-9-12 0:0:0' AND '2012-9-12 23:0:0'

解释分析的输出如下：

Hash Join  (cost=239891.03..470396.82 rows=4790498 width=51) (actual time=29203.830..45777.193 rows=4403717 loops=1)
Hash Cond: (f.flightkey = p.flightkey)
->  Seq Scan on flight_2012_09_12 f  (cost=0.00..1934.31 rows=70631 width=12) (actual time=0.014..220.494 rows=70631 loops=1)
->  Hash  (cost=158415.97..158415.97 rows=3916885 width=43) (actual time=29201.012..29201.012 rows=3950815 loops=1)
     Buckets: 2048  Batches: 512 (originally 256)  Memory Usage: 1025kB
     ->  Seq Scan on position_2012_09_12 p  (cost=0.00..158415.97 rows=3916885 width=43) (actual time=0.006..14630.058 rows=3950815 loops=1)
           Filter: ((lon < 0::double precision) AND ("time" >= '2012-09-12 00:00:00'::timestamp without time zone) AND ("time" <= '2012-09-12 23:00:00'::timestamp without time zone))
Total runtime: 58522.767 ms

我认为问题出在位置表的顺序扫描上，但我无法弄清楚它为什么会出现。以下是带索引的表结构：

               Table "public.flight_2012_09_12"
   Column       |            Type             | Modifiers 
--------------------+-----------------------------+-----------
callsign           | character varying(8)        | 
flightkey          | integer                     | 
source             | character varying(16)       | 
departure          | character varying(4)        | 
arrival            | character varying(4)        | 
original_etd       | timestamp without time zone | 
original_eta       | timestamp without time zone | 
enroute            | boolean                     | 
etd                | timestamp without time zone | 
eta                | timestamp without time zone | 
equipment          | character varying(6)        | 
diverted           | timestamp without time zone | 
time               | timestamp without time zone | 
lat                | double precision            | 
lon                | double precision            | 
altitude           | character varying(7)        | 
altitude_ft        | integer                     | 
speed              | character varying(4)        | 
asdi_acid          | character varying(4)        | 
enroute_eta        | timestamp without time zone | 
enroute_eta_source | character varying(1)        | 
Indexes:
"flight_2012_09_12_flightkey_idx" btree (flightkey)
"idx_2012_09_12_altitude_ft" btree (altitude_ft)
"idx_2012_09_12_arrival" btree (arrival)
"idx_2012_09_12_callsign" btree (callsign)
"idx_2012_09_12_departure" btree (departure)
"idx_2012_09_12_diverted" btree (diverted)
"idx_2012_09_12_enroute_eta" btree (enroute_eta)
"idx_2012_09_12_equipment" btree (equipment)
"idx_2012_09_12_etd" btree (etd)
"idx_2012_09_12_lat" btree (lat)
"idx_2012_09_12_lon" btree (lon)
"idx_2012_09_12_original_eta" btree (original_eta)
"idx_2012_09_12_original_etd" btree (original_etd)
"idx_2012_09_12_speed" btree (speed)
"idx_2012_09_12_time" btree ("time")

          Table "public.position_2012_09_12"
Column    |            Type             | Modifiers 
-------------+-----------------------------+-----------
 callsign    | character varying(8)        | 
 flightkey   | integer                     | 
 time        | timestamp without time zone | 
 lat         | double precision            | 
 lon         | double precision            | 
 altitude    | character varying(7)        | 
 altitude_ft | integer                     | 
 course      | integer                     | 
 speed       | character varying(4)        | 
 trackerkey  | integer                     | 
 the_geom    | geometry                    | 
Indexes:
"index_2012_09_12_altitude_ft" btree (altitude_ft)
"index_2012_09_12_callsign" btree (callsign)
"index_2012_09_12_course" btree (course)
"index_2012_09_12_flightkey" btree (flightkey)
"index_2012_09_12_speed" btree (speed)
"index_2012_09_12_time" btree ("time")
"position_2012_09_12_flightkey_idx" btree (flightkey)
"test_index" btree (lon)
"test_index_lat" btree (lat)

我无法想到任何其他重写查询的方法，所以在这一点上我感到困惑。如果当前设置已经是最好的了，那就这样吧，但在我看来，它应该比目前快得多。非常感谢任何帮助。

- TheOx

---

您能提供 public.position_2012_09_12 表的 lon 和 time 列的统计数据吗？也许一些 (time) where lon <0 索引会有所帮助，但在位置表中与此条件匹配的行数为 3950815。这个表里还有更多的数据吗？ - sufleR

你正在使用哪个版本的PostgreSQL？ - plang

你在执行查询之前分析了你的表吗？ - plang

预估和实际行数看起来很匹配，所以我怀疑表格不需要分析。哈希连接中的512批处理看起来很大，1024kb的内存使用量看起来很小——我想知道如果使用更大的work_mem是否会更好。除此之外，计划对我来说看起来很好，性能可能只会随着硬件改进而提高。 - David Aldridge

那个 BETWEEN 可能不会做你想要的事情 - 它将包括晚上11:00的第一秒/毫秒/纳秒（postgreSQL运行的粒度是什么）; 始终（几乎）使用排除上限(<)，特别是在时间戳中。另外，为什么只有11点（而不是直到第二天之前）？ 'time' 是一个非描述性的列名 - 也许使用类似 recordedAt 的东西会更好？ - Clockwork-Muse

Answer 1

行数估计相当合理，所以我怀疑这不是一个统计问题。

我建议尝试以下操作：

- 在 `position_2012_09_12(lon,"time")` 上创建索引或在 `position_2012_09_12("time") WHERE (lon < 0)` 上创建部分索引（如果您经常搜索 `lon < 0`）。 - 将 `random_page_cost` 设置为更低的值，例如 1.1。看看是否会更改计划，并且新计划是否实际上更快。为了测试避免顺序扫描是否更快，您可以使用 `SET enable_seqscan = off`；如果是，则更改成本参数。 - 增加此查询的 `work_mem`。在运行查询之前，使用 `SET work_mem = 10M` 或其他值进行设置。 - 如果您还没有运行最新的 PostgreSQL，请运行它。始终在问题中指定您的 PostgreSQL 版本。（编辑后更新）：您正在使用 9.1 版本，这很好。9.2 中最大的性能改进是索引仅扫描，但似乎您不太可能从索引仅扫描中获得巨大的收益。 - 如果您可以消除一些列以缩小行，则还可以稍微提高性能。虽然效果不会太大，但确实会有所改善。

- Craig Ringer

Answer 2

您之所以得到连续扫描，是因为Postgres认为这样读取的磁盘页面比使用索引更少。它可能是正确的。考虑一下，如果您使用非覆盖索引，您需要读取所有匹配的索引页面。它本质上输出了一列行标识符。然后，数据库引擎需要读取每个匹配的数据页面。

您的position表每行使用71个字节，加上任何一个geom类型占用的空间（我将假设16个字节以作说明），总共87个字节。Postgres页面大小为8192字节。因此，每页大约有90行。

您的查询匹配5563070行中的3950815行，约占总数的70%。假设数据在where过滤器方面是随机分布的，那么找到没有匹配行的数据页面的几率几乎为30%^90。这基本上是微不足道的。因此，无论您的索引有多好，您仍然必须读取所有数据页面。如果您无论如何都要读取所有页面，则表扫描通常是一个很好的方法。

唯一的出路在于我说过的非覆盖索引。如果您愿意创建可以自行回答查询的索引，则可以完全避免查找数据页面，因此您又回到了游戏中。我建议您考虑以下内容：

flight_2012_09_12 (flightkey, departure, arrival)
position_2012_09_12 (filghtkey, time, lon, ...)
position_2012_09_12 (lon, time, flightkey, ...)
position_2012_09_12 (time, long, flightkey, ...)

这里的点表示您选择的其余列。您只需要一个位置上的索引，但很难确定哪个索引最好。第一种方法可能允许在已排序数据上进行合并连接，但成本是读取整个第二个索引以执行过滤。第二种和第三种将允许对数据进行预过滤，但需要哈希连接。考虑到多少成本出现在哈希连接中，合并连接可能是一个不错的选择。

由于您的查询每行需要87字节中的52个，并且索引具有额外开销，因此您可能无法减少索引占用的空间。

另一种方法是针对“随机分布”的一面进行攻击，通过查看聚类情况。

- Laurence

---

在我看来，在航班表上添加一个覆盖索引似乎不值得，因为全扫描似乎只需要220毫秒？ - David Aldridge

@DavidAldridge 说得很有道理，不过如果覆盖索引在两个表中都以航班键开头，那么可以使用合并连接，我预计这比对预排序数据进行哈希连接更快。 - Laurence

@DavidAldridge 用户使用的是 PostgreSQL 9.1 版本，该版本不支持索引仅扫描（如覆盖索引），因此问题无关紧要。 - Craig Ringer

@CraigRinger哦，那是MVCC问题吗？如果是，9.2版本有所不同吗？ - David Aldridge

@DavidAldridge 你所指的是索引中没有可见性信息，因此Pg必须检查堆以确定行是否可见 - 这就是你所说的“MVCC问题”吗？如果是这样，9.2版本添加了仅索引扫描，可以通过使用可见性映射来避免大多数堆访问；请参阅发行说明和https://wiki.postgresql.org/wiki/Index-only_scans - Craig Ringer

@CraigRinger 谢谢你，这说明了试图从一个数据库管理系统转移知识的危险。 - Laurence