Assuming that id_temp
has not some highly skewed (in favour of your query) distribution, I don't see any way of optimizing this query.
You want 300K rows and the chances of a row matching the condition is about 1/5 (under the non-skewed assumption) so it is going to read about 1.5 million rows from the table until it find 300K matches.
Even if you have an index on id_temp
and the 300K matches are found faster, the 300K rows have to still be read from the table (as you have SELECT *
) and since they are going to be interleaved with the non-matching rows, it is still going to read about the same number of disk pages as without the index.
I think your best chances - besides improving disk I/O performance - are if you have the table partitioned using the id_temp % 5
conditionexpression in some way - but I'll leave this to Postgres experts. Perhaps some other magic will work better.
Another idea is an index with the id_temp % 5
expression as first column but this is essentially duplicating the whole 50M rows table:
CREATE INDEX id_temp_modulo_5_idx
ON calls
( (id_temp % 5), id_temp, --- all the other columns as well --- );
or a partial index if you only need the = 0
condition and never going to need =1
(or 2, 3,4). This will save about 80% space compared to the above one:
CREATE INDEX id_temp_modulo_5_equals_0_idx
ON calls
( (id_temp % 5), id_temp, --- all the other columns as well --- )
WHERE
(id_temp % 5 = 0) ;