sqlschool.gr logo


Articles of SQLschool.gr Team

PostgreSQL CTE surprise

Fivi Panopoulou
Monday 03 July 2017

Μια φορά και έναν καιρό υπήρχε ένα όχι και τόσο καλογραμμένο ομολογουμένως 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:

    SELECT recorded_date, value1 + value2 as v1, value1 * value2 as v2
    FROM dummy
WHERE recorded_date = '20160626';

Στον SQL Server αυτό που περιμένουμε να γίνει είναι εφόσον είναι εφικτό, και στην περίπτωση μας είναι, να γίνει push το predicate του where στο query του CTE, να χρησιμοποιήσει τον index που υπάρχει στο date και να μας φέρει τα 84 rows. Και αυτό κάνει όπως φαίνεται παρακάτω:

Το αντίστοιχο πλάνο σε PostgreSQL είναι το παρακάτω:

Σε αυτό παρατηρούμε απευθείας τα εξής:

  1. τον operator CTE Scan που θα δούμε τι ακριβώς είναι
  2. ότι γίνεται scan ο πίνακας, χωρίς να χρησιμοποιηθεί ο index
  3. από τον ένα 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 που ακολουθεί θα χρησιμοποιήσει το αποτέλεσμά του σαν βάση για τον υπολογισμό και τις ενέργειες που θα κάνει.

Fivi Panopoulou

Fivi Panopoulou

Fivi is a Software Engineer specializing in SQL Development. Building data flows and transforming data to enable their meaningful business interpretation is her pursuit. She works with software and reporting systems, from analysis and design to implementation and maintenance.
She has been part of Microsoft technology oriented communities since an undergraduate student. SQL School has inspired her to work with data and become a SQL Server enthusiast from her early steps.


What's New in SQL Server 2022 - Episodes

More Tips...

Become a member

If you want to receive updates from us become a member to our community.




sqlschool.gr © 2010-2024 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.