To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: =?iso-8859-1?Q?Jimmy_M=E4kel=E4?= <jimmy.makela@agent25.se>,
	"'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
Subject: Re: Unique indexes not unique? 
In-reply-to: <20030113075407.A56319-100000@megazone23.bigpanda.com> 
References: <20030113075407.A56319-100000@megazone23.bigpanda.com>
Comments: In-reply-to Stephan Szabo <sszabo@megazone23.bigpanda.com>
	message dated "Mon, 13 Jan 2003 07:58:33 -0800"
Date: Mon, 13 Jan 2003 11:12:27 -0500
Message-ID: <25544.1042474347@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Stephan Szabo <sszabo@megazone23.bigpanda.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