How to write SQL where the condition is efficient?
I have the following very large table (~ 10e8 records) ( 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 only contain non-negative integers or null. Now I need to write a query that returns orders that do not have value > 0
(i.e. order = 2
does not contain a condition, because there is a record with value = 5
).
the reverse query is simple:
SELECT order
FROM table
WHERE value > 0
The query performance is satisfactory for me.
But we cannot write
SELECT order
FROM table
WHERE value = 0
because it is possible to have an entry with the same order, but with value > 0
. The only way to find this query is:
SELECT order
FROM table
GROUP BY order
HAVING SUM(COALESCE(value, 0)) = 0
But the query is very slow due to the calculation of the sum of a very large amount of data.
Is there a way to write a query more efficiently?
source to share
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 only orders (named orders
in the query). Also, it will work best with an index table(order, value)
.
I am also wondering if the following query will 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;
distinct on
should use the index to sort by simply taking the first line encountered. The external where
will then filter them, but two scans are likely to be pretty fast.
source to share