postgres case expression with a where clause

Question

I want to filer data from my postgres database. i want to use where clause together with postgres case expressions for filtering data in a single query. i want some thing like this logic

select *from [my-table-name] where ( CASE 
     WHEN column1 = 0 THEN condition1 
     ELSE condition2
  END
) 

what i want is if my column1 is zero i want to filter those rows with condition1 and if column1 is not zero i want to filter those rows with condition2. if case expression was not used i have to write query like this

select *from [my-table-name] where column1 = 0 and condition1
select *from [my-table-name] where column1 <> 0 and condition2

for getting those required data

i tried writing seperate queries for getting those required data

Answer 1

Using CASE expressions in WHERE clauses can adversely affect query performance because doing so can prevent the execution planner from identifying opportunities to use available indexes. I'll use EXPLAIN (ANALYZE) to demonstrate the potential performance difference between using CASE expressions and pure conditional logic in the WHERE clause.

The following commands establish the demonstration environment:

CREATE TABLE case_tests (
  id serial PRIMARY KEY,
  column1 integer);

INSERT INTO case_tests (column1)
SELECT random() * 2
  FROM generate_series(1,1000000) s(n);

The first query uses CASE to determine which additional conditional test is applied:

SELECT *
  FROM case_tests
  WHERE CASE column1
          WHEN 0 THEN id <= 100
          ELSE id > 999900
        END;

Running this query with EXPLAIN (ANALYZE) returned the following:

Seq Scan on case_tests  (cost=0.00..21925.00 rows=500000 width=8) (actual time=0.014..87.607 rows=99 loops=1)
  Filter: CASE column1 WHEN 0 THEN (id <= 100) ELSE (id > 999900) END
  Rows Removed by Filter: 999901
Planning Time: 0.050 ms
Execution Time: 87.631 ms

The second query is functionally equivalent to the first, but uses pure conditional logic instead of a CASE expression:

SELECT *
  FROM case_tests
  WHERE    (column1 = 0 AND id <= 100)
        OR (column1 = 0 IS NOT TRUE AND id > 999900);

The resulting explain plan shows a total execution time about 1000 times faster than the query using CASE (0.088 ms vs. 87.631 ms):

Bitmap Heap Scan on case_tests  (cost=10.38..665.39 rows=95 width=8) (actual time=0.025..0.051 rows=99 loops=1)
  Recheck Cond: ((id <= 100) OR (id > 999900))
  Filter: (((column1 = 0) AND (id <= 100)) OR (((column1 = 0) IS NOT TRUE) AND (id > 999900)))
  Rows Removed by Filter: 101
  Heap Blocks: exact=2
  ->  BitmapOr  (cost=10.38..10.38 rows=197 width=0) (actual time=0.016..0.017 rows=0 loops=1)
        ->  Bitmap Index Scan on case_tests_pkey  (cost=0.00..5.22 rows=106 width=0) (actual time=0.006..0.006 rows=100 loops=1)
              Index Cond: (id <= 100)
        ->  Bitmap Index Scan on case_tests_pkey  (cost=0.00..5.11 rows=91 width=0) (actual time=0.010..0.010 rows=100 loops=1)
              Index Cond: (id > 999900)
Planning Time: 0.145 ms
Execution Time: 0.088 ms