[SOLVED] Dynamic SQL vs. generic query with dynamic filters

Dynamic SQL vs. generic query with dynamic filters

I'm working on a SQL query where the filters can be dynamic, and I'm concerned about its efficiency. My current approach is as follows:

SELECT *
FROM table t
WHERE (:param1 IS NULL OR t.example = :param1)
  AND (:param2 IS NULL
    OR EXISTS (
        SELECT 1
        FROM other_table o
        WHERE o.id = t.other_id
          AND o.test = :param2
    )
);

My main concern is whether this query, particularly the EXISTS clause, could negatively impact performance.

Should I prefer dynamic sql over this kind of queries?

Solution

To get optimized query plans, dynamic SQL is typically superior, especially when queries get more complex.

For instance, if you prepare your generic query like this:

PREPARE q1 (int, text) AS
SELECT *
FROM   tbl t
WHERE (:param1 IS NULL OR t.example = :param1)  -- simple
AND   (:param2 IS NULL OR EXISTS (              -- more complex
   SELECT FROM other_table o
   WHERE  o.id = t.other_id
   AND    o.test = :param2
   ));

Then the simple case may still result in the best query plan:

EXECUTE q1(123, null);

Postgres is able to prune parts of the query plan that are obviously redundant, even when executing a prepared statement. So the whole EXISTS branch does not enter into the query plan in this case. And Postgres tries to be smart about using a generic plan or not. Read details in the manual. But your generic query also adds noise in the form of all these ugly OR's. And that has an impact, too.

Dynamic SQL would execute:

SELECT *
FROM   tbl t
WHERE  t.example = 123;

But more complex cases are more likely to get a sub-optimal plan:

EXECUTE q1(null, 'foo');

Postgres still prunes the obviously redundant part, but may use a generic plan, where it might optimize better based on constant input, and without the added OR.
Dynamic SQL typically produces a better query plan:

SELECT *
FROM   tbl t
WHERE  EXISTS (
   SELECT FROM other_table o
   WHERE  o.id = t.other_id
   AND    o.test = 'foo'
   ));

On the other hand, if a single generic query simplifies query generation, and if performance is not important (because it's fast either way), or if the generic query plan happens to turn out just right, it may still be sensible. Big IF's.