Can SQL with CASE condition use index in PostgreSQL?

When I write a SQL like SELECT * FROM table WHERE table.col && object, I could use the index I built (as it support && operator), but with

SELECT * FROM table WHERE 
    CASE 
    WHEN table.col && object = true THEN true 
    ELSE false 
    END

It seems I can’t use the index?

What is the reason of this? Is there any way to solve it? Actually I want to implement a logic like CASE WHEN a&&b = true THEN a<b ELSE a>b END with the support of index, is that possible?

Answer

I am guessing that there is not situation where a case expression would use an index in Postgres. This is because the case expression enforces an order of evaluation — which is quite hard to ensure once indexes are involved.

There might be some exceptions where the compiler recognizes that the case expression is redundant and it gets removed before the optimization phase.

That said, your case expression is useless. Just use:

SELECT *
FROM table
WHERE table.col AND object ;

Notes:

• The SQL Standard for boolean and is AND. Use it.
• = true is redundant. You can include it, but it is not necessary.
• You have two boolean columns. In many cases, an index will not be used even if available because the columns are (probably) not very selective.