Re: How does filter order relate to query optimization?

Andrew Edson <cheighlund(at)yahoo(dot)com> writes:
> I'm working on a php project that's supposed to draw information from the DB for display, and I've been requested to speed up the display as much as possible. I'm drawing data from four tables, with an additional two that I have to 'bounce' through to match the keys together. Also, I've got five direct filtering requirements, four of the 'value = X' type and a date range.

> My question is this: Would shuffling the placement of the filtering
> requirements (t1.some_key = t2.some_key and t1.some_other_value = X,
> etc.) make a difference in processing speed for the response time?

No; certainly it will make no difference how you shuffle clauses that
involve different sets of tables. If you've got clauses that wind up in
the same "Filter:" condition in the generated plan, and some of them
involve expensive functions, it might be useful to shuffle the
expensive-to-evaluate ones to the end. But in most cases that's just
micro-optimization. Usually what you want to think about for something
like this is plan optimization, ie, what order are the tables joined in
and with what join methods. Have you looked at EXPLAIN ANALYZE results
for the query?

regards, tom lane