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

问题描述

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

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 等同于=.假设您实际上是说name = 'text'.

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

索引 是关键性能.

Indexes are the key to performance.

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:

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

这应该是针对您的情况的最快查询:

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提琴.

LIMIT子句适用于整个查询.一旦发现足够的行以满足LIMIT的要求，Postgres就足够聪明不执行.因此，对于查询的 first SELECT中的匹配项，EXPLAIN ANALYZE的输出看起来像这样( 向右滚动！ ):

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

强调粗体.

不要不添加ORDER BY子句，这样会使效果无效.然后，Postgres必须先考虑所有行，然后才能返回第一行.

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.

是否有通用的解决方案?

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结果中只有一行.空隙排序的一种.

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

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