<i>"Notice how SQL forces the pipeline to be written inside-out, with operations that need to happen first happening in the from clause sub-query."</i><p>That temporary table is unnecessary: you can rewrite the query as<p><pre><code>  insert into ValuableClicksPerDMA
    select dma, count(*)
    from users, clicks, geoinfo
    where users.name = clicks.user
      and value &#62; 0
      and clicks.ipaddr = geoinfo.ipaddr
    group by dma;
</code></pre>
Perhaps the reason the SQL is running slow is that the join is on a string, users.name = clicks.user, instead of an id.<p>Also, why the disregard for the optimizer?  We trust optimizers to allocate memory well, we trust optimizers to generate code well, and we can analyze the database to see which indices partition the data the best.  Having an optimizer means that you just rerun ANALYZE and the query gets rewritten if the data changes, versus changing the procedure long after the fact to reorder tables.  (And if you want a specific table order, just use cross join or something to prevent that.)