From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bruno Wolff III <bruno@wolff.to>
Cc: John A Meinel <john@arbash-meinel.com>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: linux@alteeve.com
Cc: tobias@nordicbet.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Index ot being used
Date: Mon, 13 Jun 2005 12:22:14 -0400
Message-ID: <20542.1118679734@sss.pgh.pa.us> (raw)
In-Reply-To: <20050613154834.GA9203@wolff.to>
References: <s2ad532e.099@gwmta.wicourts.gov>
<42AD9D8D.9060503@arbash-meinel.com>
<20050613154834.GA9203@wolff.to>
> John A Meinel <john@arbash-meinel.com> wrote:
>> I don't know if there are specific reasons why not, other than just not
>> being implemented yet. It might be tricky to get it correct
Not so much tricky to get correct, as potentially expensive to test for;
it'd be quite easy to waste a lot of cycles trying to match ORDER BY
keys in multiple ways to completely-irrelevant indexes. Since this
will only be helpful for a minority of queries but the costs would be
paid on almost everything with an ORDER BY, that consideration has been
looming large in my mind.
Bruno Wolff III <bruno@wolff.to> writes:
> I think you need to be able to do two things. One is to drop constant
> expressions from order by lists. The other is when looking for an index
> to produce a specific ordering, to ingore leading constant expressions
> when comparing to the order by expressions.
I've been thinking about this some more this morning, and I think I see
how it could be relatively inexpensive to recognize x=constant
restrictions that allow ordering columns of an index to be ignored. We
are already doing 90% of the work for that just as a byproduct of trying
to match the x=constant clause to the index in the first place, so it's
mostly a matter of refactoring the code to allow that work to be reused.
I don't, however, see an equally inexpensive way to ignore ORDER BY
columns. That would imply associating the '=' operator of the
restriction clause with the '<' or '>' operator of the ORDER BY clause,
which means searching for a btree opclass that has them in common, which
is not cheap since there's no indexing on pg_amop that would allow us to
find it easily. (There are various places where we do in fact do that
sort of thing, but they aren't so performance-critical.) This doesn't
come up in the other case because we already know the relevant opclass
from the index.
I don't think the use-case has been shown that justifies doing this much
work to ignore useless ORDER BY clauses. The examples that have come up
in the past all suggest ignoring index columns not the other way 'round.
Can you make a case that we need to do that part of it?
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-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, bruno@wolff.to, john@arbash-meinel.com, Kevin.Grittner@wicourts.gov, linux@alteeve.com, tobias@nordicbet.com
Subject: Re: Index ot being used
In-Reply-To: <20542.1118679734@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