{"id":465784,"date":"2025-07-03T09:14:10","date_gmt":"2025-07-03T09:14:10","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=465784"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=465784","title":{"rendered":"On reordering expressions in Postgres<\/span>"},"content":{"rendered":"
<\/div>\n
\n
\n
\n
\n

From time to time, I come across queries with complex filters like this:<\/p>\n

SELECT * FROM table WHERE  date > min_date AND  date < now() - interval '1 day' AND  value IN Subplan AND  id = 42;<\/code><\/pre>\n
And in real life, just changing the order of these conditions can noticeably improve performance. Why? Individually, each condition is cheap. But when you’re applying them to millions of rows, that «cheap» quickly adds up. Especially once you’ve dealt with the usual suspects \u2014 like making sure the table fits into shared buffers.<\/p>\n
This effect is easiest to spot in\u00a0wide\u00a0tables \u2014 the kind with dozens of variable-length columns. I sometimes see sluggish IndexScans, and when you dig in, it turns out the performance hit comes from filtering on a column that’s the 20th field in the tuple. Just figuring out the offset of that column takes CPU time.<\/p>\n
Looking at Postgres source code, it\u2019s clear the community thought about this long ago. Back in 2002, Tom Lane reluctantly committed\u00a03779f7f<\/a>, which added a basic reordering of expressions (see\u00a0order_qual_clauses<\/code>) to push subplans to the end of the list. That made sense \u2014 subplans can depend on runtime parameters and are generally expensive to evaluate.<\/p>\n
Later, in 2007, commit\u00a05a7471c<\/a>\u00a0changed the logic. From that point on, expressions were ordered strictly by their estimated cost. This still holds today, with a small tweak from\u00a0215b43c<\/a>\u00a0for row-level security (RLS), where evaluation order needed more control within each plan node.<\/p>\n
Let\u2019s see what we get in upstream Postgres right now:<\/p>\n
CREATE TABLE test (   x integer, y numeric, w timestamp DEFAULT CURRENT_TIMESTAMP, z integer ); INSERT INTO test (x, y) SELECT gs, gs FROM generate_series(1, 1E3) AS gs; VACUUM ANALYZE test;  EXPLAIN (COSTS ON) SELECT * FROM test WHERE  z > 0 AND  w > now() AND  x < (SELECT avg(y) FROM generate_series(1,1E2) y WHERE y % 2 = x % 3) AND  x NOT IN (SELECT avg(y) FROM generate_series(1,1E2) y OFFSET 0) AND  w IS NOT NULL AND  x = 42;<\/code><\/pre>\n
If we check the\u00a0Filter<\/code>\u00a0line in the plan, we get:<\/p>\n
Filter: ((w IS NOT NULL) AND (z > 0) AND (x = 42) AND (w > now()) AND          ((x)::numeric = (InitPlan 2).col1) AND ((x)::numeric < (SubPlan 1))) <\/code><\/pre>\n
Postgres evaluates them in this exact order, left to right.<\/p>\n
Here are the estimated costs:<\/p>\n