A couple of differences:
1. A CTE, unlike a subquery, acts as an optimization "fence", meaning that
filters outside the CTE are not pushed down into it for optimization
purposes:
EXPLAIN WITH foo AS (
SELECT id, value FROM bar
)
SELECT value FROM foo WHERE id = 1;
--------------------------------------------------------------------
CTE Scan on foo (cost=9867.75..22113.94 rows=2721 width=32)
Filter: (id = 1)
CTE foo
-> Seq Scan on bar (cost=0.00..9867.75 rows=544275 width=36)
whereas:
EXPLAIN SELECT value FROM (SELECT id, value FROM bar) foo WHERE id=1;
--------------------------------------------------------------------
Index Scan using foo_pkey on bar (cost=0.42..8.44 rows=1 width=4)
Index Cond: (id = 1)
Makes little sense in this example, but there are cases where you want this
control.
2. You can define a CTE once and refer to it multiple times without
duplicating it or executing it multiple times:
WITH foo AS (SELECT id, value FROM something)
SELECT id, value FROM bar INTERSECT SELECT id, value FROM foo
UNION ALL SELECT id, value FROM baz EXCEPT SELECT id, value FROM foo;
Per PostgreSQL docs, "A useful property of WITH queries is that they are
evaluated only once per execution of the parent query, even if they are
referred to more than once by the parent query or sibling WITH queries.
Thus, expensive calculations that are needed in multiple places can be
placed within a WITH query to avoid redundant work."
E.g. this sleeps for one second, not three:
EXPLAIN ANALYZE WITH foo AS (SELECT pg_sleep(1))
SELECT * from foo UNION ALL SELECT * FROM foo UNION ALL SELECT * FROM foo;
---------------------------------------------------------------------------------------------------------
Append (cost=0.01..0.07 rows=3 width=8) (actual time=1001.068..1001.079
rows=3 loops=1)
CTE foo
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=1001.057..1001.058 rows=1 loops=1)
-> CTE Scan on foo (cost=0.00..0.02 rows=1 width=8) (actual
time=1001.068..1001.071 rows=1 loops=1)
-> CTE Scan on foo foo_1 (cost=0.00..0.02 rows=1 width=8) (actual
time=0.002..0.003 rows=1 loops=1)
-> CTE Scan on foo foo_2 (cost=0.00..0.02 rows=1 width=8) (actual
time=0.000..0.000 rows=1 loops=1)
Total runtime: 1001.128 ms
Another difference is that CTEs are way more convenient when writing SQL
manually, and make for a more readable query. That's less relevant under
SQLAlchemy, but query readability still matters when troubleshooting.
Gulli
On Thu, Dec 26, 2013 at 10:27 PM, Michael Bayer <mike...@zzzcomputing.com>wrote:
> in my experience, cte() doesn’t add any new functionality unless you use
> “WITH RECURSIVE”. Otherwise, it seems to be a syntactic nicety in SQL,
> which SQLAlchemy already provides in a different way. My impression
> then is that the users we have who need CTEs to work that aren’t recursive
> are just porting from straight SQL.
>
> There might be some other reason I’m not aware of, though.
>
>
>
>
> On Dec 26, 2013, at 5:12 PM, John Kida <jdk...@gmail.com> wrote:
>
> Can someone tell me how you would use cte() differently then subquery(),
> or when you would use one over the other. I got my self in some trouble
> today when i used a cte() which worked with SA 0.8.3 , but failed on
> production which runs SA 0.8.0.
>
> It would seem that subquery() is just like ctr() but it aliases for you
> automatically. Just thought id if there is a real reason to use one over
> the other, or if cte() is kinda just an internal building block for
> creating a subquery()
>
> Thanks
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
