I have a table with four columns
|-----|-----|-----|-----------|
| a | b | c | d |
| int | int | int | timestamp |
|-----|-----|-----|-----------|
This table contains more than 100 000 000 records. I have indices on all four columns and one compound index on (a,b,c).
If I run the following query, it works fine (few milliseconds):
SELECT
count(*) FROM my_table
WHERE
a = X AND b = Y AND c = Z
It basically returns about 3 thousand elements.
However if I want to add a condition on column d
(which is a timestamp):
SELECT
count(*) FROM my_table
WHERE
a = X AND b = Y AND c = Z AND d < '2018-01-01T00:00:00'
Then the query response time jumps to minutes.
What am I missing here ?