Re: optimizer choosing the wrong index

Hello Merlin,

thanks for your help.

> can you supply the plans on the actual tables?  the 'wrong' index
> might actually be the 'right' one if expires_on is of high cardinality
> (perhaps it's distributed badly and the table needs a stats tweak to
> make it correct).

test=# select count(*) total, count(distinct client_id) ids,
count(distinct expires_on) dates from ps;
total | ids | dates
--------+--------+--------
213645 | 123366 | 213549

I played arround with set statistics on both columns, but that didn't
seem to help. (I did run analyze)

> You can probably force the right index like this:
> explain analyze select * from ps where (client_id, expires_on) =
> ('foo', '2010-11-24'::timestamp);

That didn't seem to work either:
test=# explain select * from ps where (client_id, expires_on) =
('123', '24.11.2010'::timestamp);
QUERY PLAN
---------------------------------------------------------------------------------
Index Scan using idx_wrong on ps (cost=0.00..8.29 rows=1 width=53)
Index Cond: (expires_on = '2010-11-24 00:00:00'::timestamp without time zone)
Filter: ((client_id)::text = '123'::text)

>
> merlin
>