CODEWITHSUNDEEP
sqlpostgresqlsubquery
Andy Shih
Andy Shih

Reputation: 25

Postgres SQL performance considering filtering/pagination inside vs outside subquery

I am using Postgres 13.1, and I want to know if Query 2 below would suffer in performance.

Consider a simple table for example:

                 Table "Transaction"
    Column      |              Type              | Collation | Nullable |      Default
----------------+--------------------------------+-----------+----------+-------------------
id              | text                           |           | not null |
amount          | integer                        |           | not null |
merchant_name   | text                           |           | not null |

Consider Query 1:

SELECT *
FROM Transaction
WHERE amount > 100
LIMIT 10;

Consider Query 2:

SELECT *
FROM (
  SELECT *
  FROM Transaction
) AS "result"
WHERE "result".amount > 100
LIMIT 10;

The 2 queries have the same semantic meaning and find the same rows in the end. My preference is to write many queries in the format of Query 2 to keep complicated business logic inside the subquery, filtering/pagination outside the subquery.

However, now my concern is: If there are many many rows in this table, would Query 2 perform much worse than Query 1, or would they perform about the same because these SQL statements would end up as the same operations under the hood?

Upvotes: 0

Views: 569

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

For your simple examples, there is not an issue. What actually gets executed and optimized is a directed acyclic graph (DAG), which has little obvious relationship to the SQL syntax. In other words, the original structure of the query would not affect this type of optimization.

The specific optimization is called predicate pushdown, which means moving filtering conditions into subqueries. That would make the queries able to use indexes.

Here is an example that shows identical execution plans for the two versions, both with and without an index.

Postgres does support predicate pushdown but there are caveats:

  • It does not (necessarily) work with CTEs or views.
  • It does not (necessarily) work with aggregation queries.
  • It does not (necessarily) work with set-based queries, such as union all.

The "(necessarily)" is because optimizers are complicated beasts and they change with different versions of the database -- as well as depending on things like statistics gathered for the table.

So, you are probably safe for the specific queries in your example; however, as a general principal it might not be safe. You might want to express the queries with the filtering conditions in the subqueries -- or at least check the explain plans to see if it is necessary.

Upvotes: 1

Related Questions