title: Is there a way to return early from a select statement in postgresql?
body: <p>I take a user-provided search query and split it into tokens, then generate a <code>WHERE</code> clause that checks each token against each text column (I'll leave out the indexes here for brevity, but they are well-indexed for with gin trigrams and performance is generally good).</p>
  <p>Given a table like this</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE public.people (
      first_name text NOT NULL,
      last_name text NOT NULL,
    full_address text NOT NULL
  )
  </code></pre>
  <p>and a search query such as <code>'barry wisteria lane'</code>, I'll end up with a <code>SELECT</code> statement similar to this:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT * FROM public.people WHERE
    (
      first_name ILIKE '%barry%' OR
      last_name ILIKE '%barry%' OR
      full_address ILIKE '%barry%'
    ) AND (
      first_name ILIKE '%wisteria%' OR
      last_name ILIKE '%wisteria%' OR
      full_address ILIKE '%wisteria%'
    ) AND (
      first_name ILIKE '%lane%' OR
      last_name ILIKE '%lane%' OR
      full_address ILIKE '%lane%'
    )
    LIMIT 10
    -- this order by is psuedocode that hopefully expresses what's happening
    -- an actual example would be very long and difficult to read
    -- the result is rows that are matched due to hits on first_name are first
    ORDER BY similarity_to_tokens(first_name), similarity_to_tokens(last_name), similarity_to_tokens(full_address) DESC
  </code></pre>
  <p>this is fine and very performant. It becomes unperformant if the search query is short and returns a lot of matching rows which then need to be sorted. Sorting all these rows is a waste if there are more rows that matched on <code>first_name</code> than the <code>LIMIT</code>, because the other rows will always be discarded after the sort.</p>
  <p>It's probable that I'm thinking about this problem from the wrong angle because I'm new to database work, but what seems like a simple way of gaining performance (in a normal programming language) would be to stop checking for matches on <code>last_name</code> and <code>full_address</code> if the number of matches on <code>first_name</code> has already reached what's specified in <code>LIMIT</code>, seeing as when the rows are sorted, those that matched on first_name are first.</p>
  <p>It seems to me that setting and modifying variables is not possible in SQL, so is there another way to approach this?</p>
  <p>Currently I do it in the application layer, where I decide if I feel the query is so short it warrants a first attempt where I only check <code>first_name</code>, then do a second call to the database if too few results are returned from the first attempt. But making 2 calls for one query is ugly and makes the performance worse in the case of misses with the initial call.</p>