Re: Any better plan for this query?..

On Mon, 2009-05-18 at 19:00 -0400, Tom Lane wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> > In particular, running the tests repeatedly using
> > H.REF_OBJECT = '0000000001'
> > rather than varying the value seems likely to benefit MySQL.

One thing to note in terms of optimisation of this query is that we
perform a top-level sort at the end of the query.

Both plans for this query show an IndexScan on a two column-index, with
an Index Condition of equality on the leading column. The ORDER BY
specifies a sort by the second index column, so the top-level Sort is
superfluous in this case.

My understanding is that we don't currently eliminate superfluous
additional sorts of this kind. Now I know that is a hard subject, but it
seems straightforward to consider interesting sort order equivalence
when we have constant equality constraints.

My guess would be that MySQL does do the sort removal, in latest
version.

Dimitri's EXPLAIN ANALYZEs show differing costs for that additional
step, but the around 10% of query time looks shaveable.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support