Re: Date index not used when selecting a date range

On Tue, Jul 29, 2008 at 1:25 AM, Poul Møller Hansen <freebsd(at)pbnet(dot)dk> wrote:
>
>>> 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.

If you're running 8.3 you can create index concurrently.