Message-ID: <42AD9D8D.9060503@arbash-meinel.com>
Date: Mon, 13 Jun 2005 09:51:57 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: linux@alteeve.com, tgl@sss.pgh.pa.us, tobias@nordicbet.com,
	pgsql-performance@postgresql.org
Subject: Re: Index ot being used
References: <s2ad532e.099@gwmta.wicourts.gov>
In-Reply-To: <s2ad532e.099@gwmta.wicourts.gov>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig1B4739F3434CF4A3D7E765CE"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig1B4739F3434CF4A3D7E765CE
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Kevin Grittner wrote:

>It sure would be nice if the optimizer would consider that it had the
>leeway to add any column which was restricted to a single value to any
>point in the ORDER BY clause.  Without that, the application programmer
>has to know what indexes are on the table, rather than being able to
>just worry about the set of data they want.  Obviously, if a column can
>have only one value in the result set, adding to any point in the ORDER
>BY can't change anything but performance.  That sure sounds like
>something which should fall within the scope of an optimizer.
>
>It really should be a DBA function to add or drop indexes to tune the
>performance of queries, without requiring application programmers to
>modify the queries for every DBA adjustment.  (When you have a database
>with over 350 tables and thousands of queries, you really begin to
>appreciate the importance of this.)
>
>
I agree that having a smarter optimizer, which can recognize when an
index can be used for ORDER BY would be useful.

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 (for
instance, how do you know which columns can be added, which ones will be
constant) Perhaps you could just potentially add the WHERE items if they
have an equality constraint with a constant. But I'm guessing there are
more cases than that where the optimization could be performed.

Also, the more options you give the planner, the longer it takes on
average to plan any single query. Yes, it is beneficial for this use
case, but does that balance out slowing down all the other queries by a
tiny bit.

I'm guessing the optimization wasn't as important as some of the others
that have been done, so it hasn't been implemented yet.

John
=:->


--------------enig1B4739F3434CF4A3D7E765CE
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Cygwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCrZ2NJdeBCYSNAAMRAupyAJ9BmItUj+XItfezX2JDOVc8nVlX/ACfedtU
lWLgtWkyYIdTpfDd3A2sqhs=
=JP7u
-----END PGP SIGNATURE-----

--------------enig1B4739F3434CF4A3D7E765CE--