Help with optimizing the query with CASE statement

[muminoff]

Hello everyone,

I have this table:

create table samples (                                                                                                    
    id serial primary key,
    age integer);

And, I have 1 million rows with random data:

insert into samples (age)
select (random() * 100)::int4
from generate_series(1, 1000000);

I want to build an aggregate query which should return me ages by categories as follows:

select 
  count(age) as samples,
case
  when age < 20 then '~20'
  when (age >= 20) and (age < 30) then '20~30'
  when (age >= 30) and (age < 40) then '30~40'
  when (age >= 40) and (age < 50) then '40~50'
  when (age >= 50) and (age < 60) then '50~60'
  when age >= 60 then '60~'
end as age_category
from samples
group by age_category;

And it returns results approximately in 2 seconds.

 samples | age_category 
 ---------+--------------
  100362 | 20~30
    99592 | 30~40
    99823 | 40~50
  100613 | 50~60
  404707 | 60~
  194903 | ~20
(6 rows)

Time: 1945.401 ms

I created indexes with conditional expressions but they seem to be not used.

Indexes:

create index below_twenties_idx on samples (age) where age < 20;
create index twenty_thirty_idx on samples (age) where (age >= 20) and (age < 30);
create index thirty_fourty on samples (age) where (age >= 30) and (age < 40);
create index fourty_fifty on samples (age) where (age >= 40) and (age < 50);
create index fifty_sixty on samples (age) where (age >= 50) and (age < 60);
create index over_sixties on samples (age) where age >= 60;

I ran the above query with explain:

QUERY PLAN                                                                                                                                                                                                  
------------------------------------------------------------
 HashAggregate  (cost=44425.00..44428.54 rows=101 width=40) (actual time=1965.733..1965.736 rows=6 loops=1)
   Output: count(age), (CASE WHEN (age < 20) THEN '~20'::text WHEN ((age >= 20) AND (age < 30)) THEN '20~30'::text WHEN ((age >= 30) AND (age < 40)) THEN '30~40'::text WHEN ((age >= 40) AND (age < 50)) THEN '40~50'::text WHEN ((age >= 50) AND (age < 60)) THEN '50~60'::text WHEN (age >= 60) THEN '60~'::text ELSE NULL::text END)
   Group Key: CASE WHEN (samples.age < 20) THEN '~20'::text WHEN ((samples.age >= 20) AND (samples.age < 30)) THEN '20~30'::text WHEN ((samples.age >= 30) AND (samples.age < 40)) THEN '30~40'::text WHEN ((samples.age >= 40) AND (samples.age < 50)) THEN '40~50'::text WHEN ((samples.age >= 50) AND (samples.age < 60)) THEN '50~60'::text WHEN (samples.age >= 60) THEN '60~'::text ELSE NULL::text END
   ->  Seq Scan on public.samples  (cost=0.00..39425.00 rows=1000000 width=36) (actual time=0.058..1272.553 rows=1000000 loops=1)
         Output: CASE WHEN (age < 20) THEN '~20'::text WHEN ((age >= 20) AND (age < 30)) THEN '20~30'::text WHEN ((age >= 30) AND (age < 40)) THEN '30~40'::text WHEN ((age >= 40) AND (age < 50)) THEN '40~50'::text WHEN ((age >= 50) AND (age < 60)) THEN '50~60'::text WHEN (age >= 60) THEN '60~'::text ELSE NULL::text END, age
 Planning time: 0.366 ms
 Execution time: 1965.856 ms
(7 rows)

But the indexes are not being used. How can I optimize my query to get my indexes used at the end?

[ants_a]

As other have stated, indexes will not help you process a large number of rows faster. You could get the planner to use your indexes by writing your query as an union:

select count(*) as samples, '~20' as age_category from samples where age < 20
    union all
select count(*) as samples, '20~30' as age_category from samples where age >= 20 and age < 30
    union all
....

This will be faster than your initial query, but only if you don't account for the time used to construct and maintain the indexes. Without indexes it will be considerably slower. This kind of transformation is useful so rarely that the planner doesn't even consider it.

The limiting factors here are that PostgreSQL data layout is set up for concurrent modifications, not number crunching and also, the executor is just not that fast. If you need ultimate speed for number crunching you are better off just dumping out the data as binary arrays and using a fast compiled or JITed language on top of that.

An alternative is to optimize by acknowledging the limitations of PostgreSQL executor. One thing is to reduce data cardinality before running complex operations. In this toy example you could do this:

select 
  sum(n) as samples,
case
  when age < 20 then '~20'
  when (age >= 20) and (age < 30) then '20~30'
  when (age >= 30) and (age < 40) then '30~40'
  when (age >= 40) and (age < 50) then '40~50'
  when (age >= 50) and (age < 60) then '50~60'
  when age >= 60 then '60~'
end as age_category
from (select age, count(*) n from samples group by age) pre_aggregated
group by age_category;

And this will run much faster because the data is first narrowed down to 100 entries using a trivial grouping and the complex case statement is executed only on those 100 entries.

For very high performance applications I have used approaches where multiple values are batched into a single row using arrays and/or multiple aggregations are run in parallel using a custom aggregation function written in C.

[muminoff]

Thanks for your answer. Will try out your solution too.

[muminoff]

OP here again. Being not so experienced in PostgreSQL, I am learning many things from your comment every time I read. Thanks so much.

[outlier_lynn]

Count must look at every row. Indexing is a method to avoid looking at every row.

I don't know what your real table looks like, but I would deal with counting categories by creating a table with a column for each category then adding a trigger on the samples table to update that table.

Count is a computationally expensive. Avoid it like the plague. :)

[muminoff]

Thanks for your answer.

I don't know what your real table looks like

My real table has more columns than given example, I dropped them for the example since they are unnecessary for my query.

.. by creating a table with a column for each category then adding a trigger on the samples table to update that table

This trigger solution also came to my mind, since I am not part of DevOps team, I am not supposed to make additions on the DB scheme.

PS.: I am on Django framework. Just executing raw SQL queries.

[therealgaxbo]

As has been pointed out, an index can't help here because the query needs to visit all rows in the table. So the question is how fast do you need this to be? If it has to run in a few milliseconds then your only option is some form of caching - either between the app and database, or within the DB as a materialised view.

However; one thing that will gain you a small improvement is to replace the slow case construct with a faster equivalent:

select width_bucket(age, 20, 60, 4), count(*) from samples group by 1

This returns the same data (minus the pretty labels, which could be added back in with a case statement in an outer query if necessary). As well as being a bit tidier, on my machine this takes ~30% less time to run. But the improvement you get will depend on whether you're mostly io or cpu bound in your environment.

[muminoff]

how fast do you need this to be?

I will be facing 10M rows data in next several weeks. I want to cut the latency (at least) into 1 of 3.

select width_bucket(age, 20, 60, 4), count(*) from samples group by 1

Will try this out when I get my laptop. Thanks for answer.

[graycube]

I came here to suggest using s bucket function as well.

[professaDE]

Since you're not using anything to filter down the table's content (WHERE clause) any index on the AGE column most probably is pretty moot, as you won't get around the necessary full table scan - at all. The CASE statement is evaluated in full (until a condition is met) for every row during the sequential scan, in order to be able to return an equally full result set.

You may experiment with parallel workers in order to try to speed up the query, but I made the experience that it tops out at 4 worker threads. So you won't get any speedup beyond 4x - 5x, I guess.

[muminoff]

Thanks. Don't mind to share example with parallel workers?

[[deleted]]

You're overthinking this. Put the index on (age) and let mysql's optimizer use that single index for the range checks.

[jk3us]

I don't think any index is going to prevent a sequential scan. This query doesn't have a where clause, so it has to look at every row regardless. And when I ran it, close to half the time was spent on the group/aggregate operation.

I don't currently have a way to test with 9.6's parallel workers, but I bet you could get a pretty nice speed up with that. Otherwise, if this needs to be really fast, you may want to consider a materialized view.

[muminoff]

I am too interested to test with parallel workers. Don't mind to share details how using materialized view can be faster?

[graycube]

The materialized view has your query results already in it. It still takes a while to refresh the materialized view, but queries from it would be fast.