Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p><a href="http://www.postgresql.org/docs/current/interactive/functions-matching.html#FUNCTIONS-LIKE" rel="nofollow noreferrer"><code>LIKE</code></a> without wildcard character is equivalent to <code>=</code>. Assuming you actually meant <code>name = 'text'</code>.</p> <p><a href="http://www.postgresql.org/docs/current/interactive/indexes.html" rel="nofollow noreferrer"><strong>Indexes</strong></a> are the key to performance.</p> <h3>Test setup</h3> <pre><code>CREATE TABLE image ( image_id serial PRIMARY KEY , group_id int NOT NULL , name text NOT NULL ); </code></pre> <p>Ideally, you create two indexes (in addition to the primary key):</p> <pre><code>CREATE INDEX image_name_grp_idx ON image (name, group_id); CREATE INDEX image_grp_idx ON image (group_id); </code></pre> <p>The second <em>may</em> not be necessary, depending on data distribution and other details. Explanation here:</p> <ul> <li><a href="https://dba.stackexchange.com/a/27493/3684">Is a composite index also good for queries on the first field?</a></li> </ul> <h3>Query</h3> <p>This should be the <em>fastest possible</em> query for your case:</p> <pre><code>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; </code></pre> <p><a href="http://www.sqlfiddle.com/#!15/227cb/1" rel="nofollow noreferrer"><strong>SQL Fiddle.</strong></a></p> <p>The <code>LIMIT</code> clause applies to the whole query. Postgres is smart enough <strong>not to execute</strong> later legs of the <code>UNION ALL</code> as soon as it has found enough rows to satisfy the <code>LIMIT</code>. Consequently, for a match in the <em>first</em> <code>SELECT</code> of the query, the output of <code>EXPLAIN ANALYZE</code> looks like this (<strong><em>scroll to the right!</em></strong>):</p> <pre> 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 <b>rows=1 loops=1</b>) 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) <b>(never executed)</b> Index Cond: (name = 'name105'::text) -> Index Scan using image_grp_idx on image (cost=0.00..406.36 rows=500 width=40) <b>(never executed)</b> Index Cond: (group_id = 10) Total runtime: 0.087 ms</pre> <p>Bold emphasis mine.</p> <p><strong>Do <em>not</em> add an <code>ORDER BY</code> clause</strong>, this would void the effect. Then Postgres would have to consider all rows before returning the top row.</p> <h3>Final questions</h3> <blockquote> <p>Is there a generic solution for that?</p> </blockquote> <p>This <strong>is</strong> the generic solution. Add as many <code>SELECT</code> statements as you want.</p> <blockquote> <p>Of course it would come in handy when the search result is sorted by its relevance.</p> </blockquote> <p>There is only one row in the result with <code>LIMIT 1</code>. Kind of voids sorting.</p>
    singulars
    1. This table or related slice is empty.
    1. POWay to try multiple SELECTs till a result is available?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USErwin Brandstetter
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POWay to try multiple SELECTs till a result is available?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COVery convincing indeed. Today/yesterday I've learnt something new.
      singulars
      1. PO
      1. USdezso
    2. CO@Erin: Your query is awesome, and postgres is just, awesome! The question has `LIKE` though, not `=` in the second comparison. This may make a difference in some scenario. Don't you think.
      singulars
      1. PO
      1. USypercubeᵀᴹ
    3. CO@ypercube: Thanks. :) Did you see my first paragraph and the comment under the question concerning `LIKE` vs. `=`? The principal of `UNION ALL` stopping evaluation as soon as it has found enough rows should apply to any `SELECT` statement. If we'd be talking about *fuzzy* string matching, things would get more complicated ...
      singulars
      1. PO
      1. USErwin Brandstetter
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload