PostgreSQL(PostGIS)查询远慢于MySQL，求原因及优化方向

对包含约百万条数据的PostgreSQL（PGSQL）和MySQL数据库开展性能测试后，发现MySQL的查询执行速度明显更快，但预期应是PostgreSQL表现更优，推测遗漏了关键优化点。

分别测试了使用和不使用PostGIS函数（如ST_DistanceSphere）的经纬度距离计算查询，结果显示使用PostGIS函数的查询速度更慢，这与PostGIS应更高效的预期不符。已为PGSQL中的geom（geometry）字段创建索引，但查询仍仅执行顺序扫描，即便仅返回数据库中5-10%的数据。

两个数据库数据完全一致，以下是各查询语句、执行时间及执行计划分析：

PostgreSQL 查询测试 #

Query 1 PGSQL（最快，未使用PostGIS函数） #

WITH users_filtered AS (
SELECT id, pseudonym, city, (6371 * acos( 
                cos( radians(users.latitude) ) 
              * cos( radians( 49.5099 ) ) 
              * cos( radians( 6.74549 ) - radians(users.longitude) ) 
              + sin( radians(users.latitude) ) 
              * sin( radians( 49.5099 ) )
        ) ) as distance_in_km
FROM users
)
SELECT * FROM users_filtered;

执行计划与时间：

Seq Scan on users  (cost=0.00..124501.90 rows=1029033 width=29) (actual time=0.032..932.905 rows=1030000 loops=1)
  Buffers: shared hit=80768
Planning:
  Buffers: shared hit=361
Planning Time: 1.697 ms
Execution Time: 984.460 ms

Query 2 PGSQL（最慢，使用PostGIS函数） #

WITH users_filtered AS (
SELECT id, pseudonym, city, ST_DistanceSphere(
    CAST(point(49.5099, 6.74549) AS geometry),
    users.geom
) AS distance_in_m
FROM users
)
SELECT * FROM users_filtered;

执行计划与时间：

Gather  (cost=1000.00..10909130.85 rows=1029033 width=29) (actual time=3.739..2583.961 rows=1030000 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=81778
  ->  Parallel Seq Scan on users  (cost=0.00..10805227.55 rows=428764 width=29) (actual time=2.096..2147.072 rows=343333 loops=3)
        Buffers: shared hit=81778
Planning:
  Buffers: shared hit=19
Planning Time: 0.577 ms
Execution Time: 2696.416 ms

Query 3 PGSQL（中等，使用PostGIS函数） #

WITH users_filtered AS (
    SELECT id, pseudonym, city, ST_DistanceSphere(
        CAST(point(49.5099, 6.74549) AS geometry),
        users.geom
    ) AS distance_in_m
    FROM users
    WHERE ST_DWithin(users.geom, ST_SetSRID(ST_MakePoint(49.5099, 6.7454),4326), 200000)
)
SELECT * FROM users_filtered;

执行计划与时间：

Gather  (cost=1000.00..10806234.80 rows=103 width=29) (actual time=1.526..2071.394 rows=1030000 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=81674
  ->  Parallel Seq Scan on users  (cost=0.00..10805224.50 rows=43 width=29) (actual time=1.336..1752.659 rows=343333 loops=3)
        Filter: st_dwithin(geom, '0101000020E61000007E1D386744C14840ECC039234AFB1A40'::geometry, '200000'::double precision)
        Buffers: shared hit=81674
Planning:
  Buffers: shared hit=23
Planning Time: 1.169 ms
Execution Time: 2161.133 ms

MySQL 查询测试 #

Query 1 MySQL（最快，相同查询在PGSQL需4-5秒） #

SELECT id, pseudonym, city, ROUND(111.045 *
          degrees(acos(cos(radians(49.5099)) *
          cos(radians(users.latitude)) *
          cos(radians(6.74549) -
          radians(users.longitude)) +
          sin(radians(49.5099)) *
          sin(radians(users.latitude)
          )
        )
      )
    ) AS distance_in_km FROM users;

执行计划与时间：

-> Table scan on users  (cost=272574.70 rows=2515667) (actual time=0.121..1023.582 rows=1030000 loops=1)

Query 2 MySQL（最慢，使用ST_Distance_Sphere） #

SELECT id, pseudonym, city, ST_Distance_Sphere(
    point(49.5099, 6.74549),
    point(users.latitude, users.longitude)
) AS distance_in_m FROM users;

执行计划与时间：

-> Table scan on users (cost=272574.70 rows=2515667) (actual time=0.096..1073.000 rows=1030000 loops=1)

Query 3 MySQL（中等） #

SELECT id, pseudonym, city, (6371 * acos( 
                cos( radians(users.latitude) ) 
              * cos( radians( 49.5099 ) ) 
              * cos( radians( 6.74549 ) 
              - radians(users.longitude) ) 
              + sin( radians(users.latitude) ) 
              * sin( radians( 49.5099 ) )
        ) ) as distance_in_km
FROM users ORDER BY distance_in_km ASC;

执行计划与时间：

-> Sort: (6371 * acos((((cos(radians(users.latitude)) * cos(radians(49.5099))) * cos((radians(6.74549) - radians(users.longitude)))) + (sin(radians(users.latitude)) * sin(radians(49.5099))))))  (cost=272574.70 rows=2515667) (actual time=2142.974..2268.429 rows=1030000 loops=1)
    -> Table scan on users  (cost=272574.70 rows=2515667) (actual time=0.141..1003.752 rows=1030000 loops=1)

请问为何PGSQL比MySQL慢这么多，尤其是使用PostGIS函数时？希望能得到排查方向的指引。

内容的提问来源于stack exchange，提问作者Romek