From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: Jimmy Mäkelä <jimmy.makela@agent25.se>
Cc: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: Re: Unique indexes not unique?
Date: Mon, 13 Jan 2003 11:12:27 -0500
Message-ID: <25544.1042474347@sss.pgh.pa.us> (raw)
In-Reply-To: <20030113075407.A56319-100000@megazone23.bigpanda.com>
References: <20030113075407.A56319-100000@megazone23.bigpanda.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, sszabo@megazone23.bigpanda.com, jimmy.makela@agent25.se
Subject: Re: Unique indexes not unique?
In-Reply-To: <25544.1042474347@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox