postgresql - PostgreSQL 查询更改计划

Question

我有一个 PostgreSQL 查询，它有一个 With 子句（CTE），其中输入值是给定的（int 数据类型）。对于此查询，输出行数将与此 With 子句中给出的输入值数相同。with 子句中没有连接，主 select 子句中只有两个表。

此查询在不到 500 毫秒的时间内执行，直到输入值的数量为 531。如果超出此范围，则大约需要 15 秒。（即使对于 532 个输入值）。

在解释计划中，当输入值的数量超过 531 时，我可以看到主查询的计划从“嵌套循环左连接”更改为“哈希右连接”。

编辑：为这两种情况添加了解释分析。

如果输入值的数量是 532

Sort  (cost=45221.67..45221.68 rows=2 width=322) (actual time=15371.553..15371.576 rows=532 loops=1)
  Sort Key: fc.flashnumber DESC
  Sort Method: quicksort  Memory: 95kB
  CTE t
    ->  GroupAggregate  (cost=0.43..4489.54 rows=3586 width=22) (actual time=3.876..21.531 rows=532 loops=1)
          Group Key: flashcomment.flashnumber
          ->  Index Scan using flashcomment_ndx01 on flashcomment  (cost=0.43..4435.68 rows=3601 width=22) (actual time=3.861..18.628 rows=713 loops=1)
                Index Cond: ((flashnumber)::text = ANY ('{2020022601503,2020022601502,2020022601498,2020022601497,2020022601496,2020022601495,2020022601494,2020022601493,2020022601492,2020022601491,2020022601490,2020022620200219007292020021900630,2020021900629,2020021900628}'::text[]))
                Filter: (is_parent = 'N'::bpchar)
  ->  Nested Loop Left Join  (cost=0.43..40732.12 rows=2 width=322) (actual time=3.919..15366.603 rows=532 loops=1)
        Join Filter: (upper((fc.stamp_userid)::text) = upper((up.user_id)::text))
        Rows Removed by Join Filter: 19483968
        ->  Nested Loop  (cost=0.43..39103.24 rows=1 width=299) (actual time=3.897..34.879 rows=532 loops=1)
              ->  CTE Scan on t  (cost=0.00..71.72 rows=3586 width=90) (actual time=3.879..23.038 rows=532 loops=1)
              ->  Index Scan using flashcomment_ndx01 on flashcomment fc  (cost=0.43..10.87 rows=1 width=299) (actual time=0.012..0.016 rows=1 loops=532)
                    Index Cond: ((flashnumber)::text = (t.flashnumber)::text)
                    Filter: ((is_parent = 'N'::bpchar) AND (t.mx_gmttime = stamp_gmttime))
                    Rows Removed by Filter: 0
        ->  Seq Scan on user_profile up  (cost=0.00..1000.86 rows=35886 width=23) (actual time=0.003..6.865 rows=36625 loops=532)
Planning time: 3.728 ms
Execution time: 15371.683 ms

如果输入值的数量是 530

Sort  (cost=44660.91..44660.91 rows=2 width=322) (actual time=37.383..37.406 rows=530 loops=1)
  Sort Key: fc.flashnumber DESC
  Sort Method: quicksort  Memory: 95kB
  CTE t
    ->  GroupAggregate  (cost=0.43..4473.75 rows=3572 width=22) (actual time=3.922..8.892 rows=530 loops=1)
          Group Key: flashcomment.flashnumber
          ->  Index Scan using flashcomment_ndx01 on flashcomment  (cost=0.43..4420.10 rows=3587 width=22) (actual time=3.906..8.644 rows=709 loops=1)
                Index Cond: ((flashnumber)::text = ANY ('{2020022601503,2020022601502,2020022601498,2020022601497,2020022601496,2020022601495,2020022601494,2020022601493,2020022601492,2020022601491,2020022601490,2020022601489,2020021900629,2020021900628}'::text[]))
                Filter: (is_parent = 'N'::bpchar)
  ->  Hash Right Join  (cost=38960.19..40187.14 rows=2 width=322) (actual time=14.613..34.816 rows=530 loops=1)
        Hash Cond: (upper((up.user_id)::text) = upper((fc.stamp_userid)::text))
        ->  Seq Scan on user_profile up  (cost=0.00..1000.86 rows=35886 width=23) (actual time=0.008..6.647 rows=36625 loops=1)
        ->  Hash  (cost=38960.17..38960.17 rows=1 width=299) (actual time=14.586..14.586 rows=530 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 49kB
              ->  Nested Loop  (cost=0.43..38960.17 rows=1 width=299) (actual time=3.947..14.203 rows=530 loops=1)
                    ->  CTE Scan on t  (cost=0.00..71.44 rows=3572 width=90) (actual time=3.925..9.069 rows=530 loops=1)
                    ->  Index Scan using flashcomment_ndx01 on flashcomment fc  (cost=0.43..10.88 rows=1 width=299) (actual time=0.009..0.009 rows=1 loops=530)
                          Index Cond: ((flashnumber)::text = (t.flashnumber)::text)
                          Filter: ((is_parent = 'N'::bpchar) AND (t.mx_gmttime = stamp_gmttime))
                          Rows Removed by Filter: 0
Planning time: 3.721 ms
Execution time: 37.531 ms

你能帮我看看如何提高性能吗？

标签： postgresqlpostgresql-10postgresql-performance