PostgreSQL AS MATERIALIZED CTE

[florentcigolotti]

Hi,

First, I would like to thank you a lot for the work you put in bun, it is awesome.

Here is the context: I work with bun with PostgreSQL and I am using a lot of CTEs. The query optimizer since PG 12 is inlining (if possible) the JOINS in the main query, and that is creating performance issues because it is mixing joins from main request and CTEs. It is possible to prevent the inlining with different approach but the one I am interesting in is to create an optimization barrier by using AS MATERIALIZED while defining a new CTE (see here).

I did a simple change in bun to implement a WithMaterialized (based on tag v1.2.8):

diff --git a/query_base.go b/query_base.go
index 08ff8e5..3763277 100644
--- a/query_base.go
+++ b/query_base.go
@@ -16,6 +16,13 @@ import (
 	"github.com/uptrace/bun/schema"
 )
 
+type WithType int
+
+const (
+	materialized WithType = iota
+	notMaterialized
+)
+
 const (
 	forceDeleteFlag internal.Flag = 1 << iota
 	deletedFlag
@@ -26,6 +33,7 @@ type withQuery struct {
 	name      string
 	query     schema.QueryAppender
 	recursive bool
+	withType  WithType
 }
 
 // IConn is a common interface for *sql.DB, *sql.Conn, and *sql.Tx.
@@ -254,6 +262,16 @@ func (q *baseQuery) addWith(name string, query schema.QueryAppender, recursive b
 		name:      name,
 		query:     query,
 		recursive: recursive,
+		withType:  notMaterialized,
+	})
+}
+
+func (q *baseQuery) addWithMaterialized(name string, query schema.QueryAppender, recursive bool) {
+	q.with = append(q.with, withQuery{
+		name:      name,
+		query:     query,
+		recursive: recursive,
+		withType:  materialized,
 	})
 }
 
@@ -301,7 +319,11 @@ func (q *baseQuery) appendCTE(
 		b = append(b, ")"...)
 	}
 
-	b = append(b, " AS ("...)
+	if cte.withType == materialized {
+		b = append(b, " AS MATERIALIZED ("...)
+	} else {
+		b = append(b, " AS ("...)
+	}
 
 	b, err = cte.query.AppendQuery(fmter, b)
 	if err != nil {
@@ -1390,7 +1412,6 @@ func (q *orderLimitOffsetQuery) addOrder(orders ...string) {
 			q.order = append(q.order, schema.UnsafeIdent(order))
 		}
 	}
-
 }
 
 func (q *orderLimitOffsetQuery) addOrderExpr(query string, args ...interface{}) {
diff --git a/query_select.go b/query_select.go
index 11761bb..eaa78c5 100644
--- a/query_select.go
+++ b/query_select.go
@@ -78,6 +78,11 @@ func (q *SelectQuery) With(name string, query schema.QueryAppender) *SelectQuery
 	return q
 }
 
+func (q *SelectQuery) WithMaterialized(name string, query schema.QueryAppender) *SelectQuery {
+	q.addWithMaterialized(name, query, false)
+	return q
+}
+
 func (q *SelectQuery) WithRecursive(name string, query schema.QueryAppender) *SelectQuery {
 	q.addWith(name, query, true)
 	return q

It is working perfectly fine. But I do think this is not acceptable because it is PostgreSQL specific.

• Do you think it is possible to do this without modifying bun itself? I can fork it but would prefer to stay upstream
• Do you think there is a room for implementing this in bun itself?

Thanks a lot for your feedbacks

[j2gg0s]

For PostgreSQL, this is a very useful feature.

Bun doesn’t mind providing dialect-specific methods — you can already see some existing examples of this. query_select.go I don’t believe we can offer a fully consistent API across different dialects unless we give up a lot of useful features.

I strongly hope the API can provide direct feedback to users who use it incorrectly, rather than simply ignoring them. But this is an existing issue, and you don’t need to worry about solving it.

[florentcigolotti]

Hi @j2gg0s, thanks a lot for the reply,

I didn't even see there are existing dialect-specific methods on MySQL side, so that change my previous statement and it totally makes sense to add those. In my case, that would be hard to live without this feature.

Is that fine if I submit a pull request to implement the WITH MATERIALIZED? We could then discuss on the implementation within the PR. Or I let you do it as you've been assigning you on the issue?

[j2gg0s]

PRs are always welcome.

Bun is an open-source community project, and we don’t impose overly strict constraints on the code. Most of the time, it just needs to pass lint checks.

[github-actions[bot]]

This issue has been automatically marked as stale because it has not had activity in the last 30 days. If there is no update within the next 7 days, this issue will be closed.