Way to try multiple SELECTs till a result is available?

by

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:

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.

More Related Contents:

  1. Optimize GROUP BY query to retrieve latest row per user
  2. Any downsides of using data type “text” for storing strings?
  3. Finding similar strings with PostgreSQL quickly
  4. Best way to delete millions of rows by ID
  5. Postgres not using index when index scan is much better option
  6. How do I speed up counting rows in a PostgreSQL table?
  7. SELECT DISTINCT is slower than expected on my table in PostgreSQL
  8. Sort by column ASC, but NULL values first?
  9. Using an Alias column in the where clause in Postgresql
  10. Grouped LIMIT in PostgreSQL: show the first N rows for each group?
  11. Optimize query with OFFSET on large table
  12. How to concatenate columns in a Postgres SELECT?
  13. updating table rows in postgres using subquery
  14. Find overlapping date ranges in PostgreSQL
  15. PostgreSQL multi INSERT…RETURNING with multiple columns
  16. Multiple CTE in single query
  17. Return pre-UPDATE column values using SQL only
  18. PostgreSQL equivalent for TOP n WITH TIES: LIMIT “with ties”?
  19. Selecting data into a Postgres array
  20. SQL syntax term for ‘WHERE (col1, col2) < (val1, val2)'
  21. Get the distinct sum of a joined table column
  22. Combine two tables for one output
  23. Aggregate a single column in query with many columns
  24. Aggregating (x,y) coordinate point clouds in PostgreSQL
  25. PostgreSQL convert columns to rows? Transpose?
  26. Regular expression in PostgreSQL LIKE clause
  27. Postgres Error: More than one row returned by a subquery used as an expression
  28. PostgreSQL: Full Text Search – How to search partial words?
  29. Temporal database design, with a twist (live vs draft rows)
  30. How to bulk insert only new rows in PostreSQL

Leave a Comment