主页 / dba / 问题 / 252596
Accepted
Anthony
Asked: 2019-11-06 07:18:01 +0800 CST

如何优化一张大表和一张小表的内连接查询

  • 772

我有 15M 记录的表 A 和 5k 记录的表 B。我需要对两者执行内部连接，但查询时间相当长。

explain analyze 
SELECT distinct(a.student_id), b.student_name, a.class_year
FROM table_a a
INNER JOIN table_b b on a.student_id = b.student_id;

解释计划

                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=3628096.85..3779293.37 rows=11780855 width=50) (actual time=35421.004..50690.702 rows=5078 loops=1)
   ->  Sort  (cost=3628096.85..3665895.98 rows=15119652 width=50) (actual time=35421.002..46385.451 rows=14264755 loops=1)
         Sort Key: a.student_id, b.student_name, a.class_year
         Sort Method: external merge  Disk: 890528kB
         ->  Hash Join  (cost=242.20..1308298.78 rows=15119652 width=50) (actual time=3.877..22332.795 rows=14264755 loops=1)
               Hash Cond: ((a.student_id)::text = (b.student_id)::text)
               ->  Seq Scan on table_a a  (cost=0.00..1268336.52 rows=15119652 width=25) (actual time=0.035..6168.042 rows=15119652 loops=1)
               ->  Hash  (cost=174.31..174.31 rows=5431 width=45) (actual time=3.822..3.822 rows=5431 loops=1)
                     Buckets: 8192  Batches: 1  Memory Usage: 483kB
                     ->  Seq Scan on table_b b  (cost=0.00..174.31 rows=5431 width=45) (actual time=0.008..1.886 rows=5431 loops=1)
 Planning time: 2.386 ms
 Execution time: 50822.593 ms
(12 rows)

我有一个table_a索引student_id

"student_id" btree (student_id)
postgresql index

2 个回答

  1. Best Answer

    您可以尝试首先获取您感兴趣的行，然后进行连接。假设您想要每个学生具有最高 class_year 的行，您可以尝试：

    select a.student_id, b.student_name, a.class_year
from (
  SELECT distinct on (student_id) student_id, class_year
  FROM table_a
  ORDER BY student_id, class_year desc
) a 
  JOIN table_b b on a.student_id = b.student_id;

    或者您可以尝试使用 GROUP BY，这可以使用 Postgres 11 以来的并行聚合来完成：

    select a.student_id, b.student_name, a.class_year
from (
  SELECT student_id, max(class_year) as class_year
  FROM table_a
  GROUP BY student_id
) a 
  JOIN table_b b on a.student_id = b.student_id;

    上的索引table_a (student_id, class_year)应该对此有所帮助。

    • 1

  2. 在我看来，正在使用最好的计划：

    1. 先扫描小表B做哈希桶，
    2. 然后扫描大表 A 以通过哈希从 B 中找到匹配的行。

    你可能会遇到 I/O 问题，15 秒处理 1500 万条记录，虽然如果我假设表 A 的每一行大约 100 字节长，那么在 50 秒内扫描它的吞吐量约为 30 MB/s，这是在普通硬件上并没有异常，这取决于其他情况。

    然后，是的，你花了很多时间在 DISTINCT 排序上。如果您加入并获得多个匹配项，但您对复制根本不感兴趣，那么这意味着您实际上并不需要连接表的列，除非您的模式未标准化。

    似乎这就是线索。

    SELECT distinct a.student_id, b.student_name, a.class_year
  FROM table_a a
  INNER JOIN table_b b on a.student_id = b.student_id;

    该查询似乎没有经过逻辑思考。您将获得所有这些不同的班级年份，但从 table_A 中没有其他任何内容。查看您的最终结果数字，它显示“rows=5078”，大约是 table_B 的大小。您可能应该从这个庞大的 table_A 中规范化所有这些重复的 class_years。

    • 0

相关问题