Subject need your advise to optimize an easy query
Author svanderclock

i m facing a problem with a easy thing.
i have a table with 10 000 000 rows

Table Property
Date timestamp;
LocalisationID Varchar(100);
Price NUMERIC(15,5);

all columns have an index on it.

to gave you an idea, you can query this database from

all the result need to be ordered by TimeStamps. we need only the 200 first records, and max 100 ms by query

now when i play myself with the plan i can always found a way to retrieve the result in < 100 ms. but my probleme is to do it automatiquely

for exemple

select * from Property where LocalisationID = 'xxx' and price > 100000 and price < 1500000 and beds > 3 order by Date desc

their is more than one plan we can choose (and before to try i can not really say with will be the best) :

Plan (PROPERTY ORDER index_LocalisationID_date);
Plan (PROPERTY ORDER index_LocalisationID_date INDEX (index_price));
Plan (PROPERTY ORDER index_LocalisationID_date INDEX (index_beds));
Plan (PROPERTY ORDER index_LocalisationID_date INDEX (index_price, index_beds));

or other usefull plan i forget ?

with index_LocalisationID_date = index on LocalisationID, date desc

now the problem is with plan to choose ?? (ie: not let firebird choose himself, he will most of the time choose the wrong)

if lot of property in the location 'xxx' have price > 100000 and price < 1500000 then using INDEX (index_price) is bad, and slow the query (by 5 times i see). in a opposite way if only few property have a price in this range then using the index index_price is very good ! this is same problem for every colum (baths, sqft, beds, etc...)

the automatic thing will be to do before a select count(x) from Property where price > 100000 and price < 1500000 and LocalisationID = 'xxx' to know if it's will be good to use or not the index! but i can not do this because the select count is really to much slow !

So how you will do ? i take any opinion/idea ...

thanks you by advance