sql - 相同的查询，不同的表，postgres 上的不同执行时间

Question

我遇到了 Postgres 的性能问题。我有两个具有相同结构、相同索引的表，我还在两个表的 id_coordinate 索引上执行了相同的 CLUSTER。这些表具有以下结构:

     Column     |   Type   |                  Modifiers                | Storage | Description
----------------+----------+-------------------------------------------+---------+-------------
 id_best_server | integer  | not null default nextval('seq'::regclass) | plain   |
 date           | date     | not null                                  | plain   |
 id_coordinate  | integer  | not null                                  | plain   |
 mnc            | smallint |                                           | plain   |
 id_cell        | integer  |                                           | plain   |
 rx_level       | real     |                                           | plain   |
 rx_quality     | real     |                                           | plain   |
 sqi            | real     |                                           | plain   |

Indexes:
    "history_best_server_until_2013_10_pkey" PRIMARY KEY, btree (id_best_server)
    "ix_history_best_server_until_2013_10_id_coordinate" btree (id_coordinate) CLUSTER
    "ix_history_best_server_until_2013_10_id_best_server" btree (id_best_server)

执行的查询:

EXPLAIN ANALYZE SELECT DISTINCT ON (x, y) x, y, rx_level, rx_quality, date, mnc, id_cell
FROM
(
    SELECT X(co.location) AS x, Y(co.location) AS y, tems.rx_level, tems.rx_quality, date, mnc, id_cell
    FROM tems.history_best_server_until_2012_10 AS tems
    JOIN gis.coordinate AS co ON tems.id_coordinate = co.id_coordinate
        AND co.location && setsrid(makeBox2d(GeomFromText('POINT(101000 461500)', 2710),
                             GeomFromText('POINT(102400 463610)', 2710)
                             ), 2710)
    WHERE mnc = 41
) AS j1
ORDER BY x, y, date DESC

两个表的行数几乎相同(大约 8M)。当我执行上面的查询时，在一张表上我得到了这些结果:

"Unique  (cost=245742.87..245805.99 rows=8416 width=118) (actual time=3420.966..3425.584 rows=10009 loops=1)"
"  ->  Sort  (cost=245742.87..245763.91 rows=8416 width=118) (actual time=3420.963..3422.236 rows=10212 loops=1)"
"        Sort Key: (x(co.location)), (y(co.location)), tems.date"
"        Sort Method: quicksort  Memory: 1182kB"
"        ->  Hash Join  (cost=61069.15..245194.20 rows=8416 width=118) (actual time=191.365..3405.590 rows=10212 loops=1)"
"              Hash Cond: (tems.id_coordinate = co.id_coordinate)"
"              ->  Seq Scan on history_best_server_until_2012_10 tems  (cost=0.00..147705.35 rows=3226085 width=22) (actual time=0.009..1749.468 rows=3230507 loops=1)"
"                    Filter: (mnc = 41)"
"              ->  Hash  (cost=60697.73..60697.73 rows=29714 width=104) (actual time=46.828..46.828 rows=31806 loops=1)"
"                    Buckets: 4096  Batches: 1  Memory Usage: 1864kB"
"                    ->  Bitmap Heap Scan on coordinate co  (cost=937.22..60697.73 rows=29714 width=104) (actual time=14.975..35.561 rows=31806 loops=1)"
"                          Recheck Cond: (location && '0103000020960A000001000000050000000000000080A8F84000000000F02A1C410000000080A8F84000000000E84B1C41000000000000F94000000000E84B1C41000000000000F94000000000F02A1C410000000080A8F84000000000F02A1C41'::geome (...)"
"                          ->  Bitmap Index Scan on ix_coordinate_location  (cost=0.00..929.79 rows=29714 width=0) (actual time=14.593..14.593 rows=31806 loops=1)"
"                                Index Cond: (location && '0103000020960A000001000000050000000000000080A8F84000000000F02A1C410000000080A8F84000000000E84B1C41000000000000F94000000000E84B1C41000000000000F94000000000F02A1C410000000080A8F84000000000F02A1C41'::g (...)"
"Total runtime: 3426.635 ms"

在另一张 table 上，它看起来像这样:

"Unique  (cost=267070.35..267138.75 rows=9120 width=118) (actual time=172.333..177.232 rows=10051 loops=1)"
"  ->  Sort  (cost=267070.35..267093.15 rows=9120 width=118) (actual time=172.330..173.708 rows=10256 loops=1)"
"        Sort Key: (x(co.location)), (y(co.location)), tems.date"
"        Sort Method: quicksort  Memory: 1186kB"
"        ->  Nested Loop  (cost=937.22..266470.49 rows=9120 width=118) (actual time=14.876..156.322 rows=10256 loops=1)"
"              ->  Bitmap Heap Scan on coordinate co  (cost=937.22..60697.73 rows=29714 width=104) (actual time=14.788..29.510 rows=31806 loops=1)"
"                    Recheck Cond: (location && '0103000020960A000001000000050000000000000080A8F84000000000F02A1C410000000080A8F84000000000E84B1C41000000000000F94000000000E84B1C41000000000000F94000000000F02A1C410000000080A8F84000000000F02A1C41'::geometry)"
"                    ->  Bitmap Index Scan on ix_coordinate_location  (cost=0.00..929.79 rows=29714 width=0) (actual time=14.409..14.409 rows=31806 loops=1)"
"                          Index Cond: (location && '0103000020960A000001000000050000000000000080A8F84000000000F02A1C410000000080A8F84000000000E84B1C41000000000000F94000000000E84B1C41000000000000F94000000000F02A1C410000000080A8F84000000000F02A1C41'::geometr (...)"
"              ->  Index Scan using ix_history_best_server_until_2013_10_id_coordinate on history_best_server_until_2013_10 tems  (cost=0.00..6.91 rows=1 width=22) (actual time=0.003..0.003 rows=0 loops=31806)"
"                    Index Cond: (id_coordinate = co.id_coordinate)"
"                    Filter: (mnc = 41)"
"Total runtime: 178.280 ms"

总运行时间不同。

如果不使用“WHERE mnc = 41”，它们都工作得很快。我不知道是什么导致了第一种情况下的序列扫描。请注意，mnc 只能具有 3 个可能值之一。每个值的频率在较快的表上约为 41%、39%、20%，在较慢的表上约为 43%、41%、16%。

添加:这是快速表的统计信息。

             tablename             |    attname     | n_distinct | correlation | most_common_freqs
-----------------------------------+----------------+------------+-------------+-------------------
 history_best_server_until_2013_10 | id_best_server |         -1 |           1 |
 history_best_server_until_2013_10 | date           |       1122 |   -0.206991 | many values
 history_best_server_until_2013_10 | id_coordinate  |  -0.373645 |           1 | many values
 history_best_server_until_2013_10 | mnc            |          3 |     0.30477 | {0.411783,0.386967,0.20125}
 history_best_server_until_2013_10 | id_cell        |       5811 |  -0.0759416 | many values
 history_best_server_until_2013_10 | rx_level       |      14961 |   -0.122292 | many values
 history_best_server_until_2013_10 | rx_quality     |         16 |    0.360472 | many values
 history_best_server_until_2013_10 | sqi            |       5552 |    0.212023 | many values
(8 rows)

这个是慢的:

             tablename             |    attname     | n_distinct | correlation | most_common_freqs
-----------------------------------+----------------+------------+-------------+-------------------
 history_best_server_until_2012_10 | id_best_server |         -1 |           1 |
 history_best_server_until_2012_10 | date           |        954 |   -0.205897 | many values
 history_best_server_until_2012_10 | id_coordinate  |  -0.421911 |           1 | many values
 history_best_server_until_2012_10 | mnc            |          3 |    0.314319 | {0.4349,0.402433,0.162667}
 history_best_server_until_2012_10 | id_cell        |       5617 |  -0.0715787 | many values
 history_best_server_until_2012_10 | rx_level       |      14129 |   -0.115288 | many values
 history_best_server_until_2012_10 | rx_quality     |         22 |    0.368943 | many values
 history_best_server_until_2012_10 | sqi            |       5320 |    0.226596 | many values

gis.coordinate 的表定义

                                                  Table "gis.coordinate"
    Column     |   Type   |                               Modifiers                                | Storage | Description
---------------+----------+------------------------------------------------------------------------+---------+-------------
 id_coordinate | integer  | not null default nextval('gis.coordinate_id_coordinate_seq'::regclass) | plain   |
 location      | geometry |                                                                        | main    |

Indexes:
    "coordinate_pkey" PRIMARY KEY, btree (id_coordinate)
    "ix_pk_coordinate" UNIQUE, btree (id_coordinate) CLUSTER
    "ix_coordinate_location" gist (location)

Check constraints:
    "enforce_dims_location" CHECK (ndims(location) = 2)
    "enforce_geotype_location" CHECK (geometrytype(location) = 'POINT'::text OR location IS NULL)
    "enforce_srid_location" CHECK (srid(location) = 2710)

Answer 1

这不是相同的数据，因此期望相同的计划是不合理的，除非统计数据(mnc = 41 的行数等等，值在整个表中的分布方式等)相似。

在一种情况下，该值很可能出现并遍布整个地方，而在另一种情况下，它们的分组范围很窄。在第一种情况下，seq 扫描行通常会更快；另一方面，索引扫描通常会更快。