On Mon, 2009-05-18 at 19:00 -0400, Tom Lane wrote: > Simon Riggs <simon@xxxxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
