PostgreSQL select optimization

Question

Can you please explain, how I can optimize select like this:

SELECT * FROM calls WHERE id_temp % 5 = 0 LIMIT 300000

This select is working on table with 50 millions of records.

Thanks a lot and have a nice day.

Answer 1

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 expression 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) ;