3 ms·
Their very first example has issues: > users > | where like(email, 'gmail') > | count becomes > WITH > "__subquery0" AS ( > SELECT > * > F
by mcphage 3y ago
Their very first example has issues:
> 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.
- kccqzy 3y agoShould not be a problem on modern Postgres.
- paulddraper 3y agoThat is the same query plan for any contemporary query planner. (Just like any C compiler will produce the same output for `x += 2` and `x += 1 + 1`.) --- A notable exception was PostgreSQL prior to version 12, which treated CTEs as an optimization fences.
- throwanem 3y agoI'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.
- paulddraper 3y agoIt's possible for sure. 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.)
- throwanem 3y agoGranted, 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.
- deleted 3y ago[deleted]