How to execute a 2nd query if the first one returns nothing?

Question

I basically have 2 queries to return data, the first query is fast but doesn't work in 5% of cases, the second query works for all cases but is a lot slower

How to code it in a way that it always returns a result but avoids running the second query if the first query produces a result? By result I mean anything between one row and 1000 rows

My current query which only runs the first query and returns an empty result set looks like the following:

SELECT CASE WHEN x.column1 IS NOT NULL THEN x.column1 ELSE y.column1 END column1,
       CASE WHEN x.column1 IS NOT NULL THEN x.column2 ELSE y.column2 END column2
FROM (SELECT column1, column2 FROM mytable WHERE < 1st SET OF complex conditions >) x
   , (SELECT column1, column2 FROM mytable WHERE < 2nd SET OF complex conditions >) y

By complex conditions I mean a series of GIS functions such as st_intersects and st_dwithin that would baloon this question. The output is the same for both queries when the first one returns something. I can't run both at the same time otherwise it runs for too long and users time out.

My current query is not registering the x.column1 not returning anything, and because of that the set of conditions returned by the y query are never taken into account, how to fix this?

Edit: again the intended result is when query1 (represented by x) produces an empty result set, I need to take results from running query2 (represented by y).

Please provide sample data, desired results, and a clear explanation of what you want to do. — Gordon Linoff, Aug 06 '21 at 16:20
Be aware that your query with an implicit, unrestricted `CROSS JOIN` would be incorrect in any case. (My answer does not have the same problem.) — Erwin Brandstetter, Aug 06 '21 at 17:06

Erwin Brandstetter · Accepted Answer · 2021-08-06T17:02:58.200

7

For multiple rows

WITH fast AS (
   SELECT column1, column2
   FROM   mytable
   WHERE  <1st SET OF complex conditions>
   )
, slow AS (
   SELECT column1, column2
   FROM   mytable
   WHERE  NOT EXISTS (TABLE fast)
   AND    <2nd SET OF complex conditions>
   )
TABLE fast
UNION ALL
TABLE slow;

Test with EXPLAIN ANALYZE: you'll see (never executed) for the second query (CTE slow) if the first one (CTE fast) returns any rows. I think that's what you are aiming for.

TABLE fast is just (standard SQL) short syntax for SELECT * FROM fast. See:

Is there a shortcut for SELECT * FROM?

Simpler for a single returned row

If both queries can only return a single row (or nothing), there is a simple, efficient solution without CTEs:

(
SELECT column1, column2
FROM   mytable
WHERE  <1st SET OF complex conditions>
)
UNION ALL
(
SELECT column1, column2
FROM   mytable
WHERE  <2nd SET OF complex conditions>
)
LIMIT 1;

answered Aug 06 '21 at 16:24

Erwin Brandstetter

605,456
145
1,078
1,228

Thanks for the answer but I've edited the op. The queries produce multiple rows – Luffydude Aug 06 '21 at 16:38
Unfortunately `TABLE` does not work for table-returning functions. – Stefanov.sm Aug 06 '21 at 20:41
@Stefanov.sm: Yes, unfortunately. Calling functions is subtly different internally. But that's unrelated to this question, and it's just an unimportant convenience feature, really. – Erwin Brandstetter Aug 06 '21 at 21:18
Fair enough, so it is . – Stefanov.sm Aug 07 '21 at 06:46
Thank you so much! actually it is working fine in my function. I made it work by doing select x.col1, x.col2 from (your solution) x – Luffydude Aug 07 '21 at 11:15

Stefanov.sm · Answer 2 · 2021-08-07T06:52:19.933

Here is an alternative that might be a bit less efficient but I hope clear and easy to read. A plpgsql function would do this - return the results of the fast and simple query. If there is a result (found variable) then leave, else return the results of the slow and complex query. There may be third, fourth and so on queries using the same pattern. Below is an illustration.

create or replace function _function()
returns table (<columns list>) language plpgsql as 
$$
begin 
    return query <fast-and-simple-query>;
    if found then return; end if;
    return query <slow-and-complex-query>;
end;
$$;

and then

select * from _function();

It will work for both single-row and multi-row results.

Proof of concept

create temporary table t1(x integer);
create temporary table t2(x integer);
insert into t1 values (11),(12),(13);
insert into t2 values (21),(22),(23);

create or replace function pg_temp.delme(l integer)
returns table (i integer, s text) language plpgsql as 
$$
begin 
    return query select x, 'fast' from t1 where x > l;
    if found then return; end if;
    return query select x, 'slow' from t2;
    
end;
$$;

select * from pg_temp.delme(1);
select * from pg_temp.delme(20);

How to execute a 2nd query if the first one returns nothing?

2 Answers2

For multiple rows

Simpler for a single returned row