尝试多个 SELECT 直到结果可用的方法? [英] Way to try multiple SELECTs till a result is available?

问题描述

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

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

是否可以只用一种表达方式来做到这一点?

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

解决方案

LIKE 不带通配符等价于 =.假设您实际上是指 name = 'text'.

索引是关键性能.

测试设置

创建表格图像(image_id 串行主键, group_id int NOT NULL, 名称文本 NOT NULL);

理想情况下，您创建两个索引(除了主键):

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

第二个可能不是必需的，这取决于数据分布和其他细节.此处解释:

• 复合索引是否也适用于第一个字段的查询?

查询

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

SELECT * FROM image WHERE name = 'name105' AND group_id = 10联合所有SELECT * FROM image WHERE name = 'name105'联合所有SELECT * FROM image WHERE group_id = 10限制 1；

SQL 小提琴.

LIMIT 子句适用于整个查询.Postgres 足够聪明，不执行UNION ALL 的后面部分，只要它找到足够的行来满足LIMIT.因此，对于查询的 first SELECT 中的匹配项，EXPLAIN ANALYZE 的输出如下所示(scroll向右！):

<前>限制(cost=0.00..0.86 rows=1 width=40)(实际时间=0.045..0.046 rows=1 loops=1)缓冲区:本地命中=4-> 结果(成本=0.00..866.59 行=1002 宽度=40)(实际时间=0.042..0.042 行=1 循环=1)缓冲区:本地命中=4-> Append (cost=0.00..866.59 rows=1002 width=40) (实际时间=0.039..0.039 rows=1 loops=1)缓冲区:本地命中=4-> 使用 image_name_grp_idx 对图像进行索引扫描(成本=0.00..3.76 行=2 宽度=40)(实际时间=0.035..0.035 行=1 循环=1)索引条件:((name = 'name105'::text) AND (group_id = 10))缓冲区:本地命中=4-> 使用 image_name_grp_idx 对图像进行索引扫描(成本=0.00..406.36 行=500 宽度=40)(从未执行)索引条件:(名称 = 'name105'::text)-> 使用 image_grp_idx 对图像进行索引扫描(成本=0.00..406.36 行=500 宽度=40)(从未执行)索引条件:(group_id = 10)总运行时间:0.087 毫秒

粗体强调我的.

不要不要添加ORDER BY子句，否则效果会失效.然后 Postgres 在返回顶行之前必须考虑所有行.

最后的问题

<块引用>

有没有通用的解决方案?

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

<块引用>

当然，当搜索结果按相关性排序时，它会派上用场.

结果中只有一行 LIMIT 1.一种空洞排序.

What if I want to search for a single row in a table with a decrementing precision, e.g. like this:

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

When this gives me no result, try this one:

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

And when this gives me no result, try this one:

SELECT * FROM image WHERE group_id = 10 LIMIT 1

Is it possible to do that with just one expression?

Also there arises a problem when I have not two but e.g. three or more search parameters. Is there a generic solution for that? Of course it would come in handy when the search result is sorted by its relevance.

解决方案

LIKE without wildcard character is equivalent to =. Assuming you actually meant name = 'text'.

Indexes are the key to performance.

Test setup

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

Ideally, you create two indexes (in addition to the primary key):

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

The second may not be necessary, depending on data distribution and other details. Explanation here:

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

Query

This should be the fastest possible query for your case:

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;

SQL Fiddle.

The LIMIT clause applies to the whole query. Postgres is smart enough not to execute later legs of the UNION ALL as soon as it has found enough rows to satisfy the LIMIT. Consequently, for a match in the first SELECT of the query, the output of EXPLAIN ANALYZE looks like this (scroll to the right!):

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

Bold emphasis mine.

Do not add an ORDER BY clause, this would void the effect. Then Postgres would have to consider all rows before returning the top row.

Final questions

Is there a generic solution for that?

This is the generic solution. Add as many SELECT statements as you want.

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

There is only one row in the result with LIMIT 1. Kind of voids sorting.

这篇关于尝试多个 SELECT 直到结果可用的方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！