On 30 Duben 2014, 10:46, David Noel wrote:
> Very strange. I ran the query and it seemed slow so I rewrote it with
> a join instead. Using join it finished in 800ms. The query using the
> lateral finished in more than a minute. I guess I need to do some
> analysis on those queries to figure out why there was such a vast
> difference in performance. %10, %20, %50, even %100 differences in
> performance are huge, but for something to take nearly 100x -- %10000
> longer to complete? Something just doesn't seem right.
That is not strange at all.
In an ideal world the database would able to "understand" the semantics of
the query perfectly, and rewrite it to the best plan possible (returning
the desired result). In practice that is not the case, sadly - the planner
has limited knowledge and while it can do many clever tweaks, the way you
write a query somehow limits the options. So when you use LATERAL in the
query, it may or may not be able to rewrite it to the better plan.
To really understand what's going on here we need to see the explain plans
of the queries.
Tomas
