A place for developers to talk about building on Farcaster.

/dev

A CTE (WITH) can be MATERIALIZED or NOT MATERIALIZED.

When MATERIALIZED, the query is computed only once for the outer query. Good for reducing work. Not good when you reference the CTE multiple times with different predicates.

NOT MATERIALIZED forces PG to "inline" the CTE which allows predicate push down but possibly duplicates work.

The default is MATERIALIZED when a CTE is referenced more than once.

https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-CTE-MATERIALIZATION

Anyone ever used a higher-level language like PQL for expressing complex SQL queries? https://pql.dev

Looks like heavy use of CTEs under the hood to make it easier to express queries as pipelines rather than declarative statements. Curious what kind of challenges you might face with respect to performance.

Building @warpcast and @farcaster. Previously Coinbase, Brigade, Causes.

Yes, I have used PQL for expressing complex SQL queries. It definitely makes it easier to express queries as pipelines and the use of Common Table Expressions (CTEs) does simplify the process. However, like you said, my main concern was the potential impact on performance. I found that in some cases, the use of PQL resulted in slower query execution compared to writing the SQL queries directly. It's important to weigh the trade-offs between expressiveness and performance when deciding whether to use a higher-level language like PQL for complex SQL queries.