0

The following examples have been tested in PostgreSQL versions 11 and 12.

A few of the tables involved have around 2m rows, others not more than 250k.

I have very big and nested queries like this (not the actual query, just an example):

SELECT coalesce(column1, 1) AS label1,
       coalesce(column2, 2) AS label2,
       coalesce(column3, 3) AS label3
FROM
  (SELECT table2.some_id AS label4,
                       table2.id AS label5,
                       table3.some_date AS label6,
                       CASE
                           WHEN (table2.some_value IS NOT NULL
                                 OR table2.some_other_value IS NOT NULL) THEN least(table2.some_value * 1, greatest(table4.table4, table2.some_other_value * 1))
                           ELSE table4.table4
                       END AS label6
   FROM table3
   JOIN
     (SELECT coalesce(table5.table5) AS table4,
             coalesce(table5.table5_1) AS table5_1,
             coalesce(table5.table5_2) AS table5_2,
             coalesce(table5.table5_3) AS table54_3
      FROM
        (SELECT table2.some_value * 1 AS table5,
                table2.some_id AS table5_some_id,
                table2.id AS table5_id,
                table3.some_date AS table5_some_date
         FROM table3
         JOIN table2 ON table2.some_other_id IN
           (SELECT unnest(something_nested.ids) AS unnest_1
            FROM
              (SELECT ...

The query above takes around 49s to execute.

Then I decided to convert some of these subqueries into functions. It was literally just a cut/paste of some pieces of the big query, resulting in a final query that reads like this:

SELECT coalesce(column1, 1) AS label1,
       coalesce(column2, 2) AS label2,
       coalesce(column3, 3) AS label3
FROM some_function()

-- some_function created as "STABLE":
SELECT table2.some_id AS label4,
       table2.id AS label5,
       table3.some_date AS label6,
       CASE
           WHEN (table2.some_value IS NOT NULL
                 OR table2.some_other_value IS NOT NULL) THEN least(table2.some_value * 1, greatest(table4.table4, table2.some_other_value * 1))
           ELSE table4.table4
       END AS label6
FROM some_other_function()

The query above takes the same 49s to execute.

However, as soon as I changed one function (any of them) to VOLATILE, the query magically executes in less than 250ms.

My questions are:

  • Why VOLATILE is WAY faster than STABLE in this case? Is it safe to use VOLATILE?
  • Why declaring subqueries as function executes faster than just inlining them? Is there a flag or some way to optimise this kind of scenario?

EDIT

I tried to add the execution plan here but the text body is limited to 30000 characters and one plan alone is bigger than that.

This is the obfuscated execution plan for the first query (inlined subqueries) https://explain.depesz.com/s/A3mG

This is the obfuscated execution plan for the query with volatile functions that runs super fast https://explain.depesz.com/s/Yhs1

EDIT 2

This configuration did the trick, the first query now runs in 230ms.

SET enable_nestloop = off;

I'm interested to know, behind the scenes, what Postgres is doing that is making these queries run so quickly.

0
0

Volatility has to do with the expected results and the type of query so the query planner can be more efficient.

I suspect it has something to do with that your query is performing an index scan.

Without an explain it is very difficult to know what the query planner is doing. Please add this.

0
0

Probably a bad estimation of statistics that guided the planer to use a nested loop because of an underevaluation of the cardinality.

Di you try, before running your query, to updates the statistics ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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