> WITH
> "__subquery0" AS (
> SELECT
> *
> FROM
> "users"
> WHERE
> like ("email", 'gmail')
> )
> SELECT
> COUNT(*) AS "count()"
> FROM
> "__subquery0";
Fetching everything from the users table can be a ton slower than just running a count on that table, if the table is indexed on email. I had to deal with that very problem this week.
I'd be hesitant to assume the generated CTEs are always going to be amenable to optimization. The examples on the linked page are pretty trivial queries - I wonder what happens when that ceases to be the case, as seems very likely with a tool that apparently doesn't do a great deal to promote understanding what goes on under the hood.
It's just worth recognizing that SQL itself is a tool that doesn't do a great deal promote understanding what goes on under the hood. (I've witnessed that firsthand many times.)
Granted, but I've not once yet seen it help to add a second hood for things to be going on under.
If nothing else, having to write SQL tends to lead engineers to the engine manual, where they have at least a chance to become aware of the existence of query planners.
> users > | where like(email, 'gmail') > | count
becomes
> WITH > "__subquery0" AS ( > SELECT > * > FROM > "users" > WHERE > like ("email", 'gmail') > ) > SELECT > COUNT(*) AS "count()" > FROM > "__subquery0";
Fetching everything from the users table can be a ton slower than just running a count on that table, if the table is indexed on email. I had to deal with that very problem this week.