CODEWITHSUNDEEP
sqlpostgresql
stella
stella

Reputation: 2596

How to write a SQL where condition efficiently?

I have the following very large (~ 10e8 records) table (table):

+--------------------------------+
|      id      order       value |
+--------------------------------+
|      PK       int         int  |
|       1        1           1   |
|       2        2           5   |
|       3        2               |
|       4        2           0   |
+--------------------------------+

As you can see, the value column can contain only non-negative integers or null. Now, I need to write a query returning orders which don't have a value > 0 (i.e. order = 2 doesn't hold the condition, because there's the record with value = 5).

The inverse query is simple:

SELECT order
FROM table
WHERE value > 0

The performance of the query is satisfactory for me.

But we can't quite write 

SELECT order
FROM table
WHERE value = 0

because it's possible to have a record with the same order, but having value > 0. The only way I could find to write that query is that:

SELECT order
FROM table
GROUP BY order
HAVING SUM(COALESCE(value, 0)) = 0

But the query is very slow because of computing sum of very large amount of data.

Is there a way to write the query more efficiently?

Upvotes: 3

Views: 65

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

It might be faster to use exists:

select o.*
from orders o
where not exists (select 1
                  from table t
                  where t.order = o.order and t.value > 0
                 );

This assumes that you have a table with just the orders (called orders in the query). Also, it will work best with an index on table(order, value).

I also wonder if the following query would have acceptable performance with an index on table(order, value desc)

select t.*
from (select distinct on (order) t.*
      from table t
      order by order, value desc
     ) t
where value = 0;

The distinct on should use the index for the sorting, just taking the first row encountered. The outer where would then filter these, but the two scans would probably be pretty fast.

Upvotes: 7

Related Questions