Skip site navigation (1) Skip section navigation (2)

Re: Date index not used when selecting a date range

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: Poul Møller Hansen <freebsd(at)pbnet(dot)dk>
Cc: pgsql-general(at)postgresql(dot)org, "David Wilson" <david(dot)t(dot)wilson(at)gmail(dot)com>
Subject: Re: Date index not used when selecting a date range
Date: 2008-07-29 14:18:31
Message-ID: dcc563d10807290718h42b687d6ie9a2a7f099854cb8@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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.

In response to

pgsql-general by date

Next:From: Ivan ZolotukhinDate: 2008-07-29 15:25:08
Subject: interesting trigger behaviour in 8.3
Previous:From: hubert depesz lubaczewskiDate: 2008-07-29 10:42:30
Subject: Re: Right way to reject INSERTs and UPDATEs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group