Re: Unique indexes not unique?

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> In his actual query (he sent me explain results which include the query)
> he uses ::bigint on both constants.

Okay, scratch that theory.

> Limit (cost=22669.68..22669.68 rows=95 width=372)
> -> Sort (cost=22669.68..22669.68 rows=96 width=372)
> -> Index Scan using agentresults2_modified_user,
> agentresults2_modified_user, agentresults2_modified_user on agentresults
> (cost=0.00..22666.52 rows=96 width=372)

Should I guess from the index name that it is on (modified, usr) and not
on (usr, modified)? If so, the problem is that the OR-expansion code
only triggers if it has found an OR-clause that's already usable with
the index --- ie, matches the index's first column. So this index is
the wrong way 'round for

... WHERE (usr = 'svt' OR usr = 'svt1' OR usr = 'svt2')
AND modified >= 1042239600::bigint AND modified < 1042498800::bigint ...

It would be nice someday for the expansion to work in the other case
too, but I haven't thought of a way to do it that would not waste many
cycles in typical queries where there is no benefit from searching for
OR-clauses.

regards, tom lane