From: | Robert Gravsjö <robert(at)blogg(dot)se> |
---|---|
To: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Curious index selection when using a date range |
Date: | 2010-01-03 21:16:18 |
Message-ID: | 4B410922.6060900@blogg.se |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Scott Marlowe skrev 2010-01-03 22.03:
> On Sun, Jan 3, 2010 at 1:10 PM, Robert Gravsjö<robert(at)blogg(dot)se> wrote:
>> I encountered a curious thing today. Simple select queries against a fairly
>> large, ~60M rows, and active, both in reading and writing, suddenly were
>> aweful slow, from milliseconds into 10th of seconds.
>>
>> Looking a bit closer revealed that on a date condition having a between
>> 2010-01-01 00:00:00 and 2010-01-31 23:59:59 a simple datetime index was
>> choosen while if the year was switched to 2009 a composed index making use
>> of the other condition parameters as well was choosen.
>>
>> After this we ran vacuum analyze on the table which solved the issue with
>> the composed index getting used for the current year as well.
>
> Assuming the analyze part is what fixed this, then the problem is
> you're analyzing often enough. Got autovac on? What version of pgsql
> are you running?
We're using autovaccum and running PostgreSQL 8.4.1, compiled with GCC
4.3.4, on Linux kernel 2.6.31 on x86_64 arch.
/r
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2010-01-03 21:30:17 | Re: Curious index selection when using a date range |
Previous Message | Scott Marlowe | 2010-01-03 21:03:32 | Re: Curious index selection when using a date range |