SQL is a declarative language working on sets of rows. There is no IF-THEN-ELSE branching or FOR/WHILE/UNTIL loops typically found in procedural languages. However, SQL is Turing-Complete, and you can achieve similar outcomes in a declarative way.

I'm running the following example on the following table:

drop table demo;
create table demo (id bigserial, a int, b int);
create index demo_a on demo(a asc, b asc);
create index demo_b on demo(b asc, a asc);
    SELECT * FROM demo WHERE (a=1 AND b=1)
  SELECT * FROM demo WHERE (a=1)
 if $2 then
  SELECT * FROM demo WHERE (b=1)
  SELECT * FROM demo
prepare demo_where as
  SELECT * FROM demo WHERE (a=1 AND b=1) AND (     $1 AND     $2 )
 UNION ALL
  SELECT * FROM demo WHERE (a=1)         AND (     $1 AND NOT $2 )
 UNION ALL
  SELECT * FROM demo WHERE (b=1)         AND ( NOT $1 AND     $2 )
 UNION ALL
  SELECT * FROM demo                   WHERE ( NOT $1 AND NOT $2 )
    
Are you worried by a query that seem to read the same table four times? Remember that SQL is declarative. You cannot infer the execution complexity from it. With EXPLAIN you will see exactly what is executed. 
I'm calling the prepared statement for each combination of parameters:

yugabyte=# explain (costs off) execute demo_where(true,true); 
                QUERY PLAN
-------------------------------------------
 Append
   ->  Index Scan using demo_b on demo
         Index Cond: ((b = 1) AND (a = 1))
(3 rows)
yugabyte=# explain (costs off) execute demo_where(false,true);
              QUERY PLAN
---------------------------------------
 Append
   ->  Index Scan using demo_b on demo
         Index Cond: (b = 1)
(3 rows)
yugabyte=# explain (costs off) execute demo_where(false,false);
       QUERY PLAN
------------------------
 Append
   ->  Seq Scan on demo
(2 rows)
yugabyte=# explain (costs off) execute demo_where(true,false);
              QUERY PLAN
---------------------------------------
 Append
   ->  Index Scan using demo_a on demo
         Index Cond: (a = 1)
(3 rows)
    
postgres=# set plan_cache_mode to force_generic_plan;
postgres=# explain (costs on) execute demo_where(true,true);
                               QUERY PLAN
------------------------------------------------------------------------
 Append  (cost=0.00..0.02 rows=4 width=16)
   ->  Result  (cost=0.00..0.00 rows=1 width=16)
         One-Time Filter: ($1 AND $2)
         ->  Seq Scan on demo  (cost=0.00..0.00 rows=1 width=16)
               Filter: ((a = 1) AND (b = 1))
   ->  Result  (cost=0.00..0.00 rows=1 width=16)
         One-Time Filter: ($1 AND (NOT $2))
         ->  Seq Scan on demo demo_1  (cost=0.00..0.00 rows=1 width=16)
               Filter: (a = 1)
   ->  Result  (cost=0.00..0.00 rows=1 width=16)
         One-Time Filter: ((NOT $1) AND $2)
         ->  Seq Scan on demo demo_2  (cost=0.00..0.00 rows=1 width=16)
               Filter: (b = 1)
   ->  Result  (cost=0.00..0.00 rows=1 width=16)
         One-Time Filter: ((NOT $1) AND (NOT $2))
         ->  Seq Scan on demo demo_3  (cost=0.00..0.00 rows=1 width=16)
(16 rows)
    
Anyway, even if a generic plan is used, only one branch will be executed. The scenario discriminator conditions are evaluated once with a One-Time Filter, which efficiently filters out branches that do not apply. These discarded branches will be clearly indicated as (never executed) in the EXPLAIN ANALYZE output. This strategy guarantees the minimization of unnecessary processing, ensuring that only the pertinent branch is executed based on the provided conditions. In contrast to the estimated cost, the actual execution time is solely influenced by the active branch, while the rest report an actual time=0.000.
prepare demo_where as
  SELECT * FROM demo 
  WHERE  ((a=1 AND b=1) AND (     $1 AND     $2 ))
  OR     ((a=1)         AND (     $1 AND NOT $2 ))
  OR     ((b=1)         AND ( NOT $1 AND     $2 ))
  OR     (( NOT $1 AND NOT $2 ))
    QUERY PLAN
---------------------------------------------------------------------
 Index Scan using demo_b on demo  (cost=0.00..5.25 rows=10 width=16)
   Index Cond: ((b = 1) AND (a = 1))
(2 rows)
    
postgres=# set plan_cache_mode to force_generic_plan;
postgres=# explain (costs on) execute demo_where(true,true);
                                                                  QUERY PLAN                                    
----------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on demo  (cost=0.00..47.00 rows=466 width=16)
   Filter: (((a = 1) AND (b = 1) AND $1 AND $2) OR ((a = 1) AND $1 AND (NOT $2)) OR ((b = 1) AND (NOT $1) AND $2) OR ((NOT $1) AND (NOT $2)))
(2 rows)
    #yugabytedb
                  #distributed
                  #postgres
                  #database
              Performance of range queries in B-Tree and LSM indexes
                  #postgres
                  #database
                  #yugabytedb
                  #sql
              Observing CPU/RAM/IO pressure in YugabyteDB with Linux PSI on AlmaLinux8 (Pressure Stall Information)
                  #yugabytedb
                  #linux
                  #performance
      
Built on Forem — the open source software that powers DEV and other inclusive communities.
      
Made with love and Ruby on Rails. DEV Community © 2016 - 2024.