On 02/10/12 13:02, Ondrej Ivanič wrote:
> Hi,
>
> On 2 October 2012 12:33, Toby Corkindale
> <toby.corkindale@strategicdata.com.au> wrote:
>> I have a query that joins two views, and takes 28 seconds to run.
>> However if I create temporary tables that contain the contents of each view,
>> and then join them, the total time is 1.3 seconds.
>
> try "offset 0" (or you can tweak statistics collector to get better estimates):
> select ... from (select * from view offset 0) as v ....
>
> http://blog.endpoint.com/2009/04/offset-0-ftw.html
Thanks, that ran in 820ms. (Same query with subselects but without the
OFFSET 0 ran in 28370ms)
David's method is also running in 820ms. I erroneously stated it was
349ms before, but that was actually how long it took to display the
results of EXPLAIN.
I wish I could work out what's wrong with the statistics that cause the
query plan to go awry.. the tables aren't actually very large and I've
played with the statistics setup previously and it seemed right..
many thanks,
Toby