0

Is there a best practice for enforcing the order in which search conditions are applied? We have seen a few queries where the optimizer has reordered the operations and passed rows through a search condition and/or function that is unexpected and invalid.

For example:

SELECT * 
FROM table_1
join table_2 on table_1.primary_id = table_2.primary_id
join table_3 on table_2.secondary_id = table_3.secondary_id
WHERE table_3.value is json parseable and table_3.value->>'foo' = bar

Sometime this query is optimized in such a way that the JSON lookup happens prior to ensuring the value is JSON parseable.

A similar situation can occur during coercion of types:

SELECT * 
FROM table_1
join table_2 on table_1.primary_id = table_2.primary_id and table_2.category_name = 'Foo'
join table_3 on table_2.secondary_id = table_3.secondary_id
-- first do regex to ensure we get a parseable int, then check range
-- column is stored as text as it can be mixed with other non-numerical inputs
WHERE table_3.value ~ E'^\\d{1,9}$' and table_3.value::int < :max_allowed_value_setting;

It seems the type coercion search condition can be reordered before the regex search condition, and/or that values that should be filtered by table_2.category_name = 'Foo' are passed through integer conversion first because join order was switched from table_1 -> table_2 -> table_3 to table_1 -> table_3 -> table_2.

I know the query optimizer can rearrange quite a few things, but I am still not sure when this is applicable and when it can cause an issue. The optimizer is unaware of search conditions which are dependent upon one another which can lead to errors during execution.

What parts of a SQL statement in PostgreSQL can be rearranged, and is there a best (or better) practice for handling these types of scenarios?

Edit: Update terminology to use Search Conditions instead of Constraints

3
  • 1
    I suggest you use the correct terminology: what you are describing are not constraints; they are search conditions.
    – mustaccio
    Feb 23 at 13:42
  • In this case, I am not so concerned with performance (planning/execution time), but correctness of the query. Bookmarked those notes on optimization questions for the future! Certain forms of this query can result in failure depending on how the query planner rearranges evaluation. Thank you for the note on terminology! Constraint might refer to Index constraints like NOT NULL or UNIQUE. I'll use "search conditions" from now on :) Feb 26 at 17:27

3 Answers 3

1

You can put in an optimizer barrier:

WITH q AS MATERIALIZED (
   SELECT ...
   WHERE table_3.value is json parseable
)
SELECT ...
FROM q
WHERE q.value->>'foo' = bar
2
  • Thanks Laurenz - your article on cybertec-postgresql.com was helpful here! Only the new MATERIALIZED keyword will put a barrier past PG12, right? (PG11 or lower we could not control MATERIALIZED vs NOT MATERIALIZED) Would a Case/When clause also put an optimizer barrier around the logic? ` SELECT . WHERE CASE WHEN q.value ~ E '^\\d{1,9}$' THEN q.value::int < 3 ELSE false END; ` Any pros/cons between those 2 options (CTE vs Case When) Feb 26 at 17:18
  • I don't think a CASE expression will help. Before v12, all CTEs were materialized, so you'd just omit the keyword. Feb 26 at 17:27
1

Unlike in C where operator "&&" implies short circuit evaluation in a specific order, there is no such thing in SQL and the optimizer is free to evaluate your conditions in any order.

To prevent evaluation of an expression under certain conditions you can use CASE:

CREATE TABLE foo( t TEXT );
INSERT INTO foo VALUES ('123'),('hello');
SELECT t, CASE WHEN t~E'^\\d{1,9}$' THEN t::INT ELSE NULL END from foo;
   t   | case
-------+------
 123   |  123
 hello | Null
1

"If, But, Maybe and Unless ..." style thinking is not good for databases to deal with.
If a field is supposed to contain JSON, then it should always contain JSON, even if it's an empty construct, or it should be NULL, to properly indicate the absence of a meaningful value.

If you can guarantee that table_3.value will always contains JSON, then you can drop the "is json parseable" clause altogether - I'm guessing that the JSON lookup will return false rather than throwing an error.

SELECT * 
FROM table_1
JOIN table_2 ON table_1.primary_id = table_2.primary_id
JOIN table_3 ON table_2.secondary_id = table_3.secondary_id
WHERE table_3.value->>'foo' = bar ; 

If that doesn't work, then you can force the database to do one part of the query before the other, but only as a last resort.

SELECT * 
FROM 
( SELECT 
    table_1.x
  , table_2.y
  , table_3.value as value  
  , . . . 
  FROM table_1
  JOIN table_2 ON table_1.primary_id = table_2.primary_id
  JOIN table_3 ON table_2.secondary_id = table_3.secondary_id
  WHERE table_3.value IS JSON PARSEABLE 
) t0 
WHERE t0.value->>'foo' = bar ; 
5
  • In this case, the column of interest is in a highly normalized format. It is capturing a particular user input that depending on context might be JSON parseable, or might not. It is a textual data column, but could contain numerical data, JSON data, or plain text. It might also be NULL or empty string. We can constrain the possible data types via joins, but if the join order gets jumbled then the actual coerced/parsed value may not be compatible. I think your point is valid - if we had the column data constrained we could avoid the safety check which is fragile. Feb 28 at 17:16
  • Is it possible the optimizer might extract the joins from the subselect (from_collapse_limit/join_collapse_limit)? Does this provide an optimization barrier as well? I can give this a try too. Feb 28 at 17:19
  • You're accepting "anything" from the User and just storing it " as is", without cleaning / parsing / making sense of it? Your database isn't going to like you very much ;-). Also, how many people do you know who can get JSON right every, single time? Performance bottleneck? Quite possibly. That's why you need to get rid of uncertainty in your data model.
    – Phill W.
    Feb 29 at 9:02
  • An ORM is used, and validations are done frontend/backend based on a column that tracks a class of value. I assure you, users aren't typing their own JSON. :) Mar 1 at 14:26
  • A 2nd table tracks the input category and changes frontend/backend behavior. A valid solution would be to rework the data model to track different data types separately though as you suggest. Our current design does not support that though unfortunately. Mar 1 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.