Hi,
I’m writing for Zucchetti S.p.A.
We have a situation where a table has a small composite index e.g. (field1 varchar(10), field2 varchar(10)), and a wider index e.g. (field2 varchar(10), field1 varchar(10), field3 varchar(50), field4 date).
We added the second index in our production database to tune a query on a table with millions or records, checking that, when used like
“select id from tbl where field1=’fixed_val1’ and field2=’fixed_val2’, and field3 = ‘fixed_val3’ and field4 between fixed_date1 and fixed_date2”
The planner was using this new index.
In the practice we noticed that the planner was choosing the old one, anyway.
A closer look to the generated query showed that the date interval was not written as we expected by the application, but in this way:
“select id from tbl where field1=’fixed_val1’ and field2=’fixed_val2’, and field3 = ‘fixed_val3’ and field4 between (substr(‘fixed_date1’, 1, 4)||’-01-01’)::date and (substr(‘fixed_date2’, 1, 4)|| ‘-12-31’)::date”
Running explain analyse on both queries finally revealed that in this way the planner was using a first index scan on the old index, and then a sequential scan to filter the result set. (running for 3.5s instead of 0.496ms in our production database)
Anyway, the result of the two substring can be calculated once and then used in the exact same way of our first query. We placed a patch to generate the query in the right way, but I think it should be corrected also on the DB server query planner.
Attached to this email there’s a test sequence of operation to reproduce the problem with random data.
Thanks in advance,
Marco