Questi nuovi operatori potranno essere aggiunti alla clausola GROUP BY e permetteranno il raggiungimento di risultati aggregati in modo più veloce e con un codice più pulito di quanto fosse possibile fare in precedenza, utilizzando più query GROUP BY e UNION ALL.

Facciamo un esempio classico: supponiamo di voler analizzare le vendite di un negozio.

Identifichiamo quindi la vendita come il fatto da analizzare e per praticità d’esempio, consideriamo il prezzo come unica misura a cui siamo interessati. Le dimensioni della nostra analisi saranno il tempo, il luogo e il prodotto (lo script per la creazione delle tabelle e l’inserimento dei dati utilizzati in questi esempi possono essere scaricati da questo link www.dropbox.com/s/sk2n8uz3ddp01ss/create_tables_grouping_sets.sql?dl=0.

Vediamo adesso come utilizzare i nuovi operatori per condurre delle analisi sul db.

GROUPING SETS

Con GROUPING SETS è possibile poter definire insiemi differenti di campi su cui eseguire una GROUP BY e unirne insieme i risultati. Cerchiamo di comprendere con precisione il comportamento di questo operatore con un esempio.

Supponiamo di voler analizzare le vendite dei singoli negozi per anno, e di volere anche avere i dati annuali aggregati di tutti i negozi. Fino alla versione 9.4, avremo ottenuto questo risultato eseguendo UNION ALL:

SELECT l.nome_negozio, g.anno, sum(prezzo)
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY (l.nome_negozio, g.anno)
UNION ALL
SELECT NULL AS nome_negozio, g.anno, sum(prezzo)
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY anno
ORDER BY nome_negozio, anno;

Con la versione 9.5 possiamo utilizzare GROUPING SETS:

SELECT l.nome_negozio, g.anno, sum(prezzo)
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY GROUPING SETS ((l.nome_negozio, anno), anno)
ORDER BY l.nome_negozio, anno;

Come si può notare, è possibile specificare raggruppamenti su singoli campi o su insiemi di questi, purché ogni insieme sia delimitato da parentesi. L’output delle due query è identico:

 nome_negozio | anno | sum
--------------+------+-----
 Neg1         | 2014 |   7
 Neg1         | 2015 |  31
 Neg2         | 2014 |  10
 Neg2         | 2015 |  72
 Neg3         | 2014 |  20
 Neg3         | 2015 |  64
 Neg4         | 2014 |  32
 Neg4         | 2015 |  37
              | 2014 |  69
              | 2015 | 204

Tuttavia possiamo verificare come il piano della versione che usa GROUPING SETS sia nettamente migliore. Questo è il piano della versione con UNION ALL:

QUERY PLAN
    -------------------------------------------------------------------------------------------------------------------------------------------------
     Sort  (cost=359.27..362.45 rows=1270 width=68) (actual time=0.774..0.775 rows=10 loops=1)
       Sort Key: l.nome_negozio, g.anno
       Sort Method: quicksort  Memory: 25kB
       ->  Append  (cost=133.95..293.80 rows=1270 width=68) (actual time=0.379..0.708 rows=10 loops=1)
             ->  HashAggregate  (cost=133.95..147.33 rows=1070 width=68) (actual time=0.379..0.403 rows=8 loops=1)
                   Group Key: l.nome_negozio, g.anno
                   ->  Hash Join  (cost=75.80..125.93 rows=1070 width=68) (actual time=0.128..0.253 rows=32 loops=1)
                         Hash Cond: (v.id_data = g.id)
                         ->  Hash Join  (cost=24.18..59.59 rows=1070 width=68) (actual time=0.073..0.150 rows=32 loops=1)
                               Hash Cond: (v.id_luogo = l.id)
                               ->  Seq Scan on vendite v  (cost=0.00..20.70 rows=1070 width=40) (actual time=0.016..0.033 rows=32 loops=1)
                               ->  Hash  (cost=16.30..16.30 rows=630 width=36) (actual time=0.026..0.026 rows=4 loops=1)
                                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                     ->  Seq Scan on luoghi l  (cost=0.00..16.30 rows=630 width=36) (actual time=0.006..0.012 rows=4 loops=1)
                         ->  Hash  (cost=28.50..28.50 rows=1850 width=8) (actual time=0.023..0.023 rows=3 loops=1)
                               Buckets: 2048  Batches: 1  Memory Usage: 17kB
                               ->  Seq Scan on giorni g  (cost=0.00..28.50 rows=1850 width=8) (actual time=0.005..0.008 rows=3 loops=1)
             ->  Subquery Scan on "*SELECT* 2"  (cost=131.28..135.78 rows=200 width=68) (actual time=0.297..0.303 rows=2 loops=1)
                   ->  HashAggregate  (cost=131.28..133.78 rows=200 width=36) (actual time=0.294..0.297 rows=2 loops=1)
                         Group Key: g_1.anno
                         ->  Hash Join  (cost=75.80..125.93 rows=1070 width=36) (actual time=0.097..0.215 rows=32 loops=1)
                               Hash Cond: (v_1.id_data = g_1.id)
                               ->  Hash Join  (cost=24.18..59.59 rows=1070 width=36) (actual time=0.050..0.120 rows=32 loops=1)
                                     Hash Cond: (v_1.id_luogo = l_1.id)
                                     ->  Seq Scan on vendite v_1  (cost=0.00..20.70 rows=1070 width=40) (actual time=0.006..0.021 rows=32 loops=1)
                                     ->  Hash  (cost=16.30..16.30 rows=630 width=4) (actual time=0.017..0.017 rows=4 loops=1)
                                           Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                           ->  Seq Scan on luoghi l_1  (cost=0.00..16.30 rows=630 width=4) (actual time=0.004..0.008 rows=4 loops=1)
                               ->  Hash  (cost=28.50..28.50 rows=1850 width=8) (actual time=0.015..0.015 rows=3 loops=1)
                                     Buckets: 2048  Batches: 1  Memory Usage: 17kB
                                     ->  Seq Scan on giorni g_1  (cost=0.00..28.50 rows=1850 width=8) (actual time=0.003..0.006 rows=3 loops=1)
     Planning time: 1.433 ms
     Execution time: 1.330 ms

Mentre questo è il piano della versione con GROUPING SETS, più rapido e più gradevole alla vista:

QUERY PLAN
    ------------------------------------------------------------------------------------------------------------------------------------------
     Sort  (cost=271.81..274.99 rows=1270 width=68) (actual time=0.475..0.476 rows=10 loops=1)
       Sort Key: l.nome_negozio, g.anno
       Sort Method: quicksort  Memory: 25kB
       ->  GroupAggregate  (cost=179.76..206.34 rows=1270 width=68) (actual time=0.312..0.426 rows=10 loops=1)
             Group Key: g.anno, l.nome_negozio
             Group Key: g.anno
             ->  Sort  (cost=179.76..182.44 rows=1070 width=68) (actual time=0.287..0.299 rows=32 loops=1)
                   Sort Key: g.anno, l.nome_negozio
                   Sort Method: quicksort  Memory: 27kB
                   ->  Hash Join  (cost=75.80..125.93 rows=1070 width=68) (actual time=0.089..0.197 rows=32 loops=1)
                         Hash Cond: (v.id_data = g.id)
                         ->  Hash Join  (cost=24.18..59.59 rows=1070 width=68) (actual time=0.059..0.124 rows=32 loops=1)
                               Hash Cond: (v.id_luogo = l.id)
                               ->  Seq Scan on vendite v  (cost=0.00..20.70 rows=1070 width=40) (actual time=0.017..0.031 rows=32 loops=1)
                               ->  Hash  (cost=16.30..16.30 rows=630 width=36) (actual time=0.022..0.022 rows=4 loops=1)
                                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                     ->  Seq Scan on luoghi l  (cost=0.00..16.30 rows=630 width=36) (actual time=0.006..0.010 rows=4 loops=1)
                         ->  Hash  (cost=28.50..28.50 rows=1850 width=8) (actual time=0.013..0.013 rows=3 loops=1)
                               Buckets: 2048  Batches: 1  Memory Usage: 17kB
                               ->  Seq Scan on giorni g  (cost=0.00..28.50 rows=1850 width=8) (actual time=0.005..0.007 rows=3 loops=1)
     Planning time: 0.741 ms
     Execution time: 0.713 ms

Inoltre, potremmo voler raggruppate le nostre vendite per regione e categoria, con l’aggiunta di una riga che mostri il totale. In questo caso, utilizzeremo la seguente query:

SELECT l.regione, p.categoria, sum(prezzo) AS totale
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN prodotti p ON (v.id_prodotto=p.id)
GROUP BY GROUPING SETS ((l.regione, p.categoria), ())
ORDER BY regione, categoria;

Ottenendo come risultato:

 regione | categoria | totale
---------+-----------+--------
 Toscana | cat1      |    104
 Toscana | cat2      |    100
 Umbria  | cat1      |     14
 Umbria  | cat2      |     55
         |           |    273

GROUPING SETS è l’operatore più generico dei tre, e permette di raggruppare dati in base a qualsiasi insieme di parametri definito dall’utente. CUBE e ROLLUP, come vedremo, sono solo comode scorciatoie per alcuni utilizzi molto comuni di GROUPING SETS.

CUBE

L’operatore CUBE raggruppa sull’insieme di tutte le combinazioni possibili dei vari campi passati come parametro. Supponiamo, ad esempio, di voler vedere i totali delle vendite organizzati per categoria, provincia e mese/anno. Lanciando la query:

SELECT l.provincia, p.categoria, g.mese, g.anno, sum(prezzo) AS totale
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN prodotti p ON (v.id_prodotto=p.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY CUBE((g.mese, g.anno),p.categoria, l.provincia)
ORDER BY anno, mese, provincia, categoria;

otterremo il seguente risultato:

 provincia | categoria | mese | anno | totale
-----------+-----------+------+------+--------
 Firenze   | cat1      |   11 | 2014 |     20
 Firenze   |           |   11 | 2014 |     20
 Perugia   | cat1      |   11 | 2014 |      2
 Perugia   | cat2      |   11 | 2014 |     30
 Perugia   |           |   11 | 2014 |     32
 Prato     | cat1      |   11 | 2014 |     12
 Prato     | cat2      |   11 | 2014 |      5
 Prato     |           |   11 | 2014 |     17
           | cat1      |   11 | 2014 |     34
           | cat2      |   11 | 2014 |     35
           |           |   11 | 2014 |     69
 Firenze   | cat1      |   10 | 2015 |     20
 Firenze   | cat2      |   10 | 2015 |     20
 Firenze   |           |   10 | 2015 |     40
 Perugia   | cat1      |   10 | 2015 |     10
 Perugia   | cat2      |   10 | 2015 |      5
 Perugia   |           |   10 | 2015 |     15
 Prato     | cat1      |   10 | 2015 |     22
 Prato     | cat2      |   10 | 2015 |     25
 Prato     |           |   10 | 2015 |     47
           | cat1      |   10 | 2015 |     52
           | cat2      |   10 | 2015 |     50
           |           |   10 | 2015 |    102
 Firenze   | cat1      |   11 | 2015 |      4
 Firenze   | cat2      |   11 | 2015 |     20
 Firenze   |           |   11 | 2015 |     24
 Perugia   | cat1      |   11 | 2015 |      2
 Perugia   | cat2      |   11 | 2015 |     20
 Perugia   |           |   11 | 2015 |     22
 Prato     | cat1      |   11 | 2015 |     26
 Prato     | cat2      |   11 | 2015 |     30
 Prato     |           |   11 | 2015 |     56
           | cat1      |   11 | 2015 |     32
           | cat2      |   11 | 2015 |     70
           |           |   11 | 2015 |    102
 Firenze   | cat1      |      |      |     44
 Firenze   | cat2      |      |      |     40
 Firenze   |           |      |      |     84
 Perugia   | cat1      |      |      |     14
 Perugia   | cat2      |      |      |     55
 Perugia   |           |      |      |     69
 Prato     | cat1      |      |      |     60
 Prato     | cat2      |      |      |     60
 Prato     |           |      |      |    120
           | cat1      |      |      |    118
           | cat2      |      |      |    155
           |           |      |      |    273

Avremmo potuto scrivere la stessa query usando GROUPING SETS in questo modo:

SELECT l.provincia, p.categoria, g.mese, g.anno, sum(prezzo) AS totale
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN prodotti p ON (v.id_prodotto=p.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY GROUPING SETS(
  ((g.mese, g.anno),p.categoria, l.provincia),
  ((g.mese, g.anno),p.categoria),
  ((g.mese, g.anno),l.provincia),
  (p.categoria, l.provincia),
  (g.mese, g.anno),
  p.categoria,
  l.provincia,
  ()
)
ORDER BY anno, mese, provincia, categoria;

Il piano di esecuzione è lo stesso per entrambe le versioni:

QUERY PLAN
    ------------------------------------------------------------------------------------------------------
     Sort  (cost=856.15..868.35 rows=4881 width=104)
       Sort Key: g.anno, g.mese, l.provincia, p.categoria
       ->  GroupAggregate  (cost=223.60..557.12 rows=4881 width=104)
             Group Key: p.categoria, l.provincia, g.mese, g.anno
             Group Key: p.categoria, l.provincia
             Group Key: p.categoria
             Group Key: ()
             Sort Key: g.mese, g.anno, p.categoria
               Group Key: g.mese, g.anno, p.categoria
               Group Key: g.mese, g.anno
             Sort Key: l.provincia, g.mese, g.anno
               Group Key: l.provincia, g.mese, g.anno
               Group Key: l.provincia
             ->  Sort  (cost=223.60..226.28 rows=1070 width=104)
                   Sort Key: p.categoria, l.provincia, g.mese, g.anno
                   ->  Hash Join  (cost=104.92..169.76 rows=1070 width=104)
                         Hash Cond: (v.id_data = g.id)
                         ->  Hash Join  (cost=53.30..103.43 rows=1070 width=100)
                               Hash Cond: (v.id_prodotto = p.id)
                               ->  Hash Join  (cost=24.18..59.59 rows=1070 width=72)
                                     Hash Cond: (v.id_luogo = l.id)
                                     ->  Seq Scan on vendite v  (cost=0.00..20.70 rows=1070 width=44)
                                     ->  Hash  (cost=16.30..16.30 rows=630 width=36)
                                           ->  Seq Scan on luoghi l  (cost=0.00..16.30 rows=630 width=36)
                               ->  Hash  (cost=18.50..18.50 rows=850 width=36)
                                     ->  Seq Scan on prodotti p  (cost=0.00..18.50 rows=850 width=36)
                         ->  Hash  (cost=28.50..28.50 rows=1850 width=12)
                               ->  Seq Scan on giorni g  (cost=0.00..28.50 rows=1850 width=12)

ROLLUP

ROLLUP, come CUBE, è una semplificazione di GROUPING SETS. In questo caso i raggruppamenti sono effettuati prima su tutti i campi su cui è stato dichiarato l’operatore, poi su tutti meno l’ultimo, su tutti meno gli ultimi due e così via. Ad esempio, usando ROLLUP al posto di CUBE nell’ultima query otterremo:

SELECT l.provincia, p.categoria, g.mese, g.anno, sum(prezzo) AS totale
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN prodotti p ON (v.id_prodotto=p.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY ROLLUP((g.mese, g.anno),p.categoria, l.provincia)
ORDER BY anno, mese, provincia, categoria;

 provincia | categoria | mese | anno | totale
-----------+-----------+------+------+--------
 Firenze   | cat1      |   11 | 2014 |     20
 Perugia   | cat1      |   11 | 2014 |      2
 Perugia   | cat2      |   11 | 2014 |     30
 Prato     | cat1      |   11 | 2014 |     12
 Prato     | cat2      |   11 | 2014 |      5
           | cat1      |   11 | 2014 |     34
           | cat2      |   11 | 2014 |     35
           |           |   11 | 2014 |     69
 Firenze   | cat1      |   10 | 2015 |     20
 Firenze   | cat2      |   10 | 2015 |     20
 Perugia   | cat1      |   10 | 2015 |     10
 Perugia   | cat2      |   10 | 2015 |      5
 Prato     | cat1      |   10 | 2015 |     22
 Prato     | cat2      |   10 | 2015 |     25
           | cat1      |   10 | 2015 |     52
           | cat2      |   10 | 2015 |     50
           |           |   10 | 2015 |    102
 Firenze   | cat1      |   11 | 2015 |      4
 Firenze   | cat2      |   11 | 2015 |     20
 Perugia   | cat1      |   11 | 2015 |      2
 Perugia   | cat2      |   11 | 2015 |     20
 Prato     | cat1      |   11 | 2015 |     26
 Prato     | cat2      |   11 | 2015 |     30
           | cat1      |   11 | 2015 |     32
           | cat2      |   11 | 2015 |     70
           |           |   11 | 2015 |    102
           |           |      |      |    273

ovvero, l’equivalante di:

SELECT l.provincia, p.categoria, g.mese, g.anno, sum(prezzo) AS totale
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN prodotti p ON (v.id_prodotto=p.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY GROUPING SETS(
  ((g.mese, g.anno),p.categoria, l.provincia),
  ((g.mese, g.anno),p.categoria),
  (g.mese, g.anno),
  ()
)
ORDER BY anno, mese, provincia, categoria;

Conclusioni

Versione dopo versione PostgreSQL sta facendo passi da gigante in ambito OLAP e data warehouse – o, se preferite, BigData e Analytics. Oltre ai comandi mostrati in questo articolo, solo nella 9.5 ad esempio sono stati aggiunti gli indici BRIN, ereditarietà delle tabelle esterne e IMPORT FOREIGN SCHEMA, che permettono a PostgreSQL di gestire quantità ancora più grandi di dati e con più facilità.

Siamo sicuri che chi usa PostgreSQL per fare Business Intelligence apprezzerà notevolmente questa nuova major release.

Un esempio importante è rappresentato dai sistemi di business intelligence e dai sistemi di data warehouse. Nello specifico: la soluzione adottata per la memorizzazione dei dati, solitamente un RDBMS.

PostgreSQL è oggigiorno una soluzione flessibile, efficace e affidabile per la memorizzazione di grosse moli di dati, sia un singolo nodo che su un cluster di nodi distribuiti utilizzando soluzioni open-source come gli Skytools prodotti da Skype. Tra l’altro, la nota azienda di telecomunicazioni e di VoIP è da anni uno dei principali utilizzatori di PostgreSQL avendo anche basato su di esso la propria struttura informativa – capace di memorizzare fino a un miliardo di profili utente.

2ndQuadrant, sfruttando le competenze interne in materia di data warehouse e di soluzioni scalabili, ha pertanto pensato che fosse giunto il momento di dimostrare pubblicamente quanto PostgreSQL sia maturo per esigenze di questo tipo.

Lo ha fatto tramite un talk presentato al PGDay di Parigi da Gabriele Bartolini, volto a fornire una panoramica sullo stato dell’arte di PostgreSQL in materia di data warehouse e alcuni suggerimenti per l’ottimizzazione e la modellazione logica del database. Le slide, in inglese, sono scaricabili secondo la licenza Creative Commons.

Download: Slide del talk “Data warehousing with PostgreSQL” (in inglese).

Simon Riggs, CEO di 2ndQuadrant ha aperto la conferenza con il suo keynote speech sul futuro di PostgreSQL "PostgreSQL: The next 20 years".

Gabriele Bartolini, responsabile di 2ndQuadrant Italia, ha presentato un intervento sui sistemi di datawarehouse open-source con PostgreSQL.

Infine, Simon Riggs, in collaborazione con Robert Hodges di Continuent ha brillantemente esposto il futuro di PostgreSQL nel campo dell’alta disponibilità (High Availability).