时间戳

首页 > 解决方案 > SQL 需要很长时间才能执行

postgresql - SQL 需要很长时间才能执行

问题描述

如果有 OR 条件，我有一个查询需要很长时间才能完成。他在那：

select d.UID as DocUID
from dbo.DS_DOCUMENTS d  
left join ds_textstore txt on d.uid = txt.doc_uid 
where (to_tsvector('russian', str_search) @@ to_tsquery('dog') or txt.tsv @@ to_tsquery('dog'));

这是他的计划：

Hash Right Join  (cost=34406.97..41503.18 rows=347 width=16) (actual time=496.101..8360.575 rows=1 loops=1)
  Hash Cond: (txt.doc_uid = d.uid)
  Filter: ((to_tsvector('russian'::regconfig, (d.str_search)::text) @@ to_tsquery('dog'::text)) OR (txt.tsv @@ to_tsquery('dog'::text)))
  Rows Removed by Filter: 217292
  ->  Seq Scan on ds_textstore txt  (cost=0.00..6768.29 rows=124629 width=46) (actual time=0.010..34.225 rows=124851 loops=1)
  ->  Hash  (cost=31668.10..31668.10 rows=219110 width=297) (actual time=204.712..204.712 rows=214290 loops=1)
        Buckets: 262144  Batches: 1  Memory Usage: 71262kB
        ->  Seq Scan on ds_documents d  (cost=0.00..31668.10 rows=219110 width=297) (actual time=0.011..107.992 rows=214290 loops=1)
Planning time: 0.911 ms
Execution time: 8361.027 ms

我找到了一个解决方案，但它看起来不是很理想：

select d.UID as DocUID
from dbo.DS_DOCUMENTS d  
left join ds_textstore txt on d.uid = txt.doc_uid 
where txt.tsv @@ to_tsquery('dog')
union
select d.UID as DocUID
from dbo.DS_DOCUMENTS d  
where to_tsvector('russian', str_search) @@ to_tsquery('dog');

这是他的计划：

HashAggregate  (cost=1744.35..1746.93 rows=258 width=16) (actual time=0.317..0.318 rows=1 loops=1)
  Group Key: d.uid
  ->  Append  (cost=49.57..1743.71 rows=258 width=16) (actual time=0.100..0.311 rows=1 loops=1)
        ->  Nested Loop  (cost=49.57..992.56 rows=116 width=16) (actual time=0.099..0.100 rows=1 loops=1)
              ->  Bitmap Heap Scan on ds_textstore txt  (cost=49.15..489.81 rows=116 width=16) (actual time=0.086..0.086 rows=1 loops=1)
                    Recheck Cond: (tsv @@ to_tsquery('dog'::text))
                    Heap Blocks: exact=1
                    ->  Bitmap Index Scan on textsearch_idx  (cost=0.00..49.12 rows=116 width=0) (actual time=0.080..0.080 rows=1 loops=1)
                          Index Cond: (tsv @@ to_tsquery('dog'::text))
              ->  Index Only Scan using ipk__ds_documents__73ba3083 on ds_documents d  (cost=0.42..4.33 rows=1 width=16) (actual time=0.011..0.011 rows=1 loops=1)
                    Index Cond: (uid = txt.doc_uid)
                    Heap Fetches: 0
        ->  Bitmap Heap Scan on ds_documents d_1  (cost=137.35..748.56 rows=142 width=16) (actual time=0.211..0.211 rows=0 loops=1)
              Recheck Cond: (to_tsvector('russian'::regconfig, (str_search)::text) @@ to_tsquery('dog'::text))
              ->  Bitmap Index Scan on idx_document_strsearch  (cost=0.00..137.32 rows=142 width=0) (actual time=0.210..0.210 rows=0 loops=1)
                    Index Cond: (to_tsvector('russian'::regconfig, (str_search)::text) @@ to_tsquery('dog'::text))
Planning time: 0.762 ms
Execution time: 0.398 ms

我尝试使用 enable_seqscan 配置，结果只会变得更糟。也许有人有更简洁的解决方案？

标签： postgresqlfull-text-search

解决方案

您的解决方案是完美的。

ORin WHEREcondition经常是性能问题。

推荐阅读