hello,

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);
Beds SMALLINT
Baths SMALLINT
SQFT NUMERIC(12,4)

all columns have an index on it.

to gave you an idea, you can query this database from http://www.trulia.com/

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
stephane