Are non-used CTEs never materialized in PostgreSQL 16?

4 votes

1 answer

155 views

postgresql postgresql-performance dynamic-sql cte

                          I want to return my post rows in one of three sort orders, controlled by a sorting_param function parameter. The sorting is complex, doesn't lend itself to a simple ORDER BY CASE .... So I'm thinking of writing each sorting into its own CTE, and then picking one of the CTEs as the final output. However, I wouldn't want PostgreSQL to waste time on all three, just the one I selected. So will all three be materialized or only the one I picked? Or is there a better way to do this? PostgreSQL version 16. Simplified code below.

~~~pgsql
WITH posts_by_order1 AS
(
	SELECT p.id
	FROM post p
	ORDER BY [some complex ordering 1]
	LIMIT 10
),
posts_by_order2 AS
(
	SELECT p.id
	FROM post p
	ORDER BY [some complex ordering 2]
	LIMIT 10
),
posts_by_order3 AS
(
	SELECT p.id
	FROM post p
	ORDER BY [some complex ordering 3]
	LIMIT 10
)
SELECT * FROM posts_by_order1 WHERE sorting_param = 0
UNION ALL
SELECT * FROM posts_by_order2 WHERE sorting_param = 1
UNION ALL
SELECT * FROM posts_by_order3 WHERE sorting_param = 2;
~~~

Unfortunately I can't do this:

~~~pgsql
    ORDER BY 
        CASE 
            WHEN sorting_param = 0 THEN p.date 
            WHEN sorting_param = 1 THEN p.name 
            ELSE NULL 
        END DESC, ...
~~~
                        

Asked by Björn Morén (143 rep)

Nov 8, 2024, 06:43 AM
Last activity: Nov 9, 2024, 01:21 PM

Are non-used CTEs never materialized in PostgreSQL 16?

Related Questions