Μια φορά και έναν καιρό υπήρχε ένα όχι και τόσο καλογραμμένο ομολογουμένως query σε PostgreSQL. Το query αυτό έπαιζε ως που μια μέρα τα data έγιναν πολλά και άρχισε να αργεί σημαντικά. Το query αυτό περιελάμβανε ένα CTE από ο οποίο και έκανε select κάποια rows φιλτράροντας σε πεδίο που ήταν του base πίνακα.
Για να εστιάσουμε ακριβώς στο πρόβλημα θα χρησιμοποιήσουμε ένα πολύ απλούστερο παράδειγμα, παράλληλα σε postgres και SQL Server, που θα μας δείξει πως συμπεριφέρεται ένα CTE στην postgres και γιατί πρέπει να το έχουμε υπόψιν μας.
Έχουμε λοιπόν έναν dummy πίνακα με autoincrement int pk clustered, ενα τύπου date πεδίο (recorded_date) στο οποίο έχουμε πάνω index και 2 randomly generated integers.
Τον ίδιο πίνακα έχουμε δημιουργήσει και στους 2 servers και έχει μέσα ίδιο αριθμό εγγραφών (συνολικά 76677), ίδιες ημερομηνίες και αριθμό εγγραφών ανα ημερομηνία. Συγκεκριμένα, έχουμε 911 διαφορετικές ημερομηνίες και για τις 26/06/2016 έχουμε 83 rows.
Έχουμε επίσης και το παρακάτω query:
WITH A AS
(
SELECT recorded_date, value1 + value2 as v1, value1 * value2 as v2
FROM dummy
)
SELECT *
FROM A
WHERE recorded_date = '20160626';
Στον SQL Server αυτό που περιμένουμε να γίνει είναι εφόσον είναι εφικτό, και στην περίπτωση μας είναι, να γίνει push το predicate του where στο query του CTE, να χρησιμοποιήσει τον index που υπάρχει στο date και να μας φέρει τα 84 rows. Και αυτό κάνει όπως φαίνεται παρακάτω:
Το αντίστοιχο πλάνο σε PostgreSQL είναι το παρακάτω:
Σε αυτό παρατηρούμε απευθείας τα εξής:
- τον operator CTE Scan που θα δούμε τι ακριβώς είναι
- ότι γίνεται scan ο πίνακας, χωρίς να χρησιμοποιηθεί ο index
- από τον ένα operator στον άλλο περνάνε 76677 rows, δηλαδή όλα
Αυτό λοιπόν που φαίνεται να γίνεται σε γένικες γραμμές είναι να κάνει “materialize” το query του CTE και στην συνέχεια να εκτελεί το select πάνω σε αυτό. Ή όπως λέει και στο documentation τα CTEs "can be thought of as defining temporary tables that exist just for one query”.
Όπου χρησιμοποιώντας τον index θα βρει και θα περάσει στο CTE scan μόνο τα 83 rows του date που ψάχνουμε
Το συμπέρασμα όμως είναι ότι στην PostgreSQL θα υπολογιστεί κάθε CTE που ορίζουμε, όπως το ορίζουμε και στην συνέχεια το κύριο statement που ακολουθεί θα χρησιμοποιήσει το αποτέλεσμά του σαν βάση για τον υπολογισμό και τις ενέργειες που θα κάνει.