Josh Berkus <josh@xxxxxxxxxxxx> writes: > On 5/11/11 3:04 PM, Shaun Thomas wrote: >> The original query, with our very large tables, ran for over *two hours* >> thanks to a nested loop iterating over the subquery. My replacement ran >> in roughly 30 seconds. If we were using a newer version of PG, we could >> have used a CTE. But do you get what I mean? Temp tables are a fairly >> common technique, but how would a coder know about CTEs? They're pretty >> new, even to *us*. > For that matter, it would be even better if PostgreSQL realized that a > materialize of the subquery was a better execution plan, and just did it > for you. It does. I was a bit surprised that Shaun apparently got a plan that didn't include a materialize step, because when I test a similar query here, I get: 1. a hash join, until I turn off enable_hashjoin; then 2. a merge join, until I turn off enable_mergejoin; then 3. a nestloop with materialize on the subquery scan. In 9.0 and up I can get a nestloop without materialize by also turning off enable_material, but pre-9.0 there's no such option ... regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
