Re: [GENERAL] Date index not used when selecting a date range

>> This is the index:
>> CREATE INDEX idx_stat_date_node_type
>> ON public.stat
>> USING btree
>> (date, node, "type");
>>
>>
>> explain SELECT * FROM public.stat WHERE node = '1010101010' AND
>> ("date" <= '2008-06-30'::date AND "date" >= '2008-01-01'::date)
>>
>
> Try changing the index order to node, date rather than date, node. You
> need the column on which you'll be doing range checking to be the
> furthest to the right in the index column list.
>
>
Then it works. Unfortunately the production database is always in use
and it contains more than 100 mill. rows,
so creating an index is not an option.

Poul

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general