From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Martin Below <machtin(dot)below(at)googlemail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: optimizer choosing the wrong index |
Date: | 2010-07-08 18:46:50 |
Message-ID: | AANLkTin_-m9wqi0FtEW1i_aFn9cAEANlVAmNchKl5Imo@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Jul 8, 2010 at 10:11 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Martin Below <machtin(dot)below(at)googlemail(dot)com> writes:
>> test=# select count(*) total, count(distinct client_id) ids,
>> count(distinct expires_on) dates from ps;
>> total | ids | dates
>> --------+--------+--------
>> 213645 | 123366 | 213549
>
> That says the expires_on column is practically unique, which makes me
> think the planner is indeed making the right choice.
I think so too, but suppose we wanted to force the other plan anyways:
select * from ps where (client_id, expires_on) >=
('123', '24.11.2010'::timestamp) and (client_id, expires_on) < ('123',
null) order by client_id, expires_on;
I'd be curious to see explain analyze (not explain) comparisons for
the 'wrong' index vs above. I suspect the plan is 'correct' for
*most* of the data, or you cherry picked (or unluckily drew) a bad
value to get your 22 times speed difference.
merlin
From | Date | Subject | |
---|---|---|---|
Next Message | Bryan Montgomery | 2010-07-08 18:50:18 | Re: GSS Authentication |
Previous Message | smga3000 | 2010-07-08 15:30:01 | DataArchitect version 4.2 Now Available |