Comments (1)
After reviewing the documentation I am unable to determine if the RECURSIVE
keyword has any affect on the optimizations introduced in Postgres 12.
if a
WITH
query is non-recursive and side-effect-free (that is, it is aSELECT
containing no volatile functions) then it can be folded into the parent query, allowing joint optimization of the two query levels. By default, this happens if the parent query references theWITH
query just once, but not if it references theWITH
query more than once. You can override that decision by specifyingMATERIALIZED
to force separate calculation of theWITH
query, or by specifyingNOT MATERIALIZED
to force it to be merged into the parent query.
The RECURSIVE
keyword is only allowed once immediately after the WITH
keyword, while there may be multiple comma-delimited CTEs that follow, some of which may not be recursive. It is unclear whether the presence of the RECURSIVE
keyword causes all CTEs in the WITH
block to be marked as recursive by the query planner, or if a deeper analysis (something like does the CTE contain a self-referential UNION
query?) is done on each CTE in the WITH
block to determine if the query is recursive or not.
WITH RECURSIVE s(m) AS (
SELECT 0 -- is this considered recursive?
), t(n) AS (
SELECT 1
UNION ALL
SELECT n+1 FROM t -- this certainly is recursive
)
SELECT m, n FROM s, t LIMIT 10;
It seems reasonable to interpret the phrase "WITH
query is non-recursive" as applying to a single CTE within a WITH RECURSIVE
block since later in that same paragraph it says the optimization decision can be overridden "by specifying MATERIALIZED
to force separate calculation of the WITH
query" which clearly applies to a single query within a block that may contain more than one.
The SQLite documentation is more explicit, although there MATERIALIZED
and NOT MATERIALIZED
are hints that may be ignored by the query planner. Unlike Postgres, the RECURSIVE
keyword is not required, even when using rCTEs.
The SQL:1999 spec requires that the RECURSIVE keyword follow WITH in any WITH clause that includes a recursive common table expression. However, for compatibility with SqlServer and Oracle, SQLite does not enforce this rule.
from django-cte.
Related Issues (20)
- Improve documentation HOT 3
- is there a way to select from a cte without joining it with a model? HOT 5
- Usage in the context of time series HOT 1
- Combining with django-reversion HOT 2
- Can I count all children in Queryset annotate? HOT 7
- annotate() usage in recursion HOT 5
- Unneccessary outer join breaks query HOT 1
- With(empty_qs) throws Error HOT 1
- “no such column”/“column does not exist” errors in Django 4.2 HOT 9
- Not able to do full outer join with django_cte HOT 2
- hnej
- `.update()` does not work when accessing nested tables HOT 1
- Minor : typo in the doc
- How to use as default manager? HOT 1
- Prefetch objects with their nested children
- issue while trying to use with_cte on EmptyResultSet HOT 1
- issue while using cte with union querysets
- Django 4.2 error: each UNION query must have the same number of columns HOT 3
- it's wrong number when i wnat to get total number by using count()
- WITH query name specified more than once HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from django-cte.