sql - 尝试多个 SELECT 直到结果可用的方法？

Question

如果我想以递减的精度在表中搜索单行怎么办？像这样:

SELECT * FROM image WHERE name LIKE 'text' AND group_id = 10 LIMIT 1

当这没有给我结果时，试试这个:

SELECT * FROM image WHERE name LIKE 'text' LIMIT 1

当这没有给我结果时，试试这个:

SELECT * FROM image WHERE group_id = 10 LIMIT 1

是否可以只用一个表达式来做到这一点？

当我没有两个但例如三个或更多搜索参数。有通用的解决方案吗？当搜索结果按相关性排序时，它当然会派上用场。

Answer 1

测试设置

CREATE TABLE image (
  image_id serial PRIMARY KEY
, group_id int NOT NULL
, name     text NOT NULL
);

Indexes是性能的关键因素。理想情况下，除了主键之外，您还创建这两个:

CREATE INDEX image_name_grp_idx ON image (name, group_id);
CREATE INDEX image_grp_idx ON image (group_id);

第二个可能不是必需的，这取决于数据分布和其他细节。见:

• Is a composite index also good for queries on the first field?

查询

更新:当 Parallel Append 用于大集合时，这在 Postgres 11 或更高版本中变得不可靠!考虑这个问题和答案(包括我的答案中的可靠替代方案):

• Are results from UNION ALL clauses always appended in order?

---

这应该是针对您的案例的最快查询:

SELECT * FROM image WHERE name = 'name105' AND group_id = 10
UNION ALL
SELECT * FROM image WHERE name = 'name105'
UNION ALL
SELECT * FROM image WHERE group_id = 10
LIMIT  1;

fiddle
<子>旧sqlfiddle

LIKE没有通配符等同于 =

LIMIT 子句适用于整个查询。 Postgres 足够聪明，一旦找到足够的行来满足 LIMIT，不会执行 UNION ALL 的后续分支。因此，对于查询的 first SELECT 中的匹配项，EXPLAIN ANALYZE 的输出如下所示(向右滚动!):

Limit  (cost=0.00..0.86 rows=1 width=40) (actual time=0.045..0.046 rows=1 loops=1)  Buffers: local hit=4  ->  Result  (cost=0.00..866.59 rows=1002 width=40) (actual time=0.042..0.042 rows=1 loops=1)        Buffers: local hit=4        ->  Append  (cost=0.00..866.59 rows=1002 width=40) (actual time=0.039..0.039 rows=1 loops=1)              Buffers: local hit=4              ->  Index Scan using image_name_grp_idx on image  (cost=0.00..3.76 rows=2 width=40) (actual time=0.035..0.035 rows=1 loops=1)                    Index Cond: ((name = 'name105'::text) AND (group_id = 10))                    Buffers: local hit=4              ->  Index Scan using image_name_grp_idx on image  (cost=0.00..406.36 rows=500 width=40) (never executed)                    Index Cond: (name = 'name105'::text)              ->  Index Scan using image_grp_idx on image  (cost=0.00..406.36 rows=500 width=40) (never executed)                    Index Cond: (group_id = 10)Total runtime: 0.087 ms

大胆强调我的。

不要添加外部 ORDER BY 子句，这会使效果无效。然后 Postgres 在返回顶行之前必须考虑所有行。

最后的问题

Is there a generic solution for that?

这是通用解决方案。添加任意数量的 SELECT 语句。

Of course it would come in handy when the search result is sorted by its relevance.

结果中只有一行 LIMIT 1。一种空隙排序。