Database Reference
In-Depth Information
Common Table Expressions
To add readability and simpliication to much larger and complex queries,
PostgreSQL has a rich feature known as
Common Table Expressions
(
CTE
). Using
the
WITH
keyword in query, you ind a way to break down the query and attain
eficiency along with readability. What it does is that it can be used like an in-line
view and even allows recursion to your SQL statements. Though for recursion,
you have to use the
WITH RECURSIVE
keyword instead of simply using
WITH
for
your queries. We will see how CTE can be used and its resultant plan node. In the
following example, we will create a
test_cte_scan
table and using PostgreSQL's
generate_series()
function, we will will fabricate some data:
CREATE TABLE test_cte_scan
(
customer_id INT NOT NULL,
order_date TIMESTAMP NOT NULL,
filler_txt TEXT NOT NULL
);
INSERT INTO test_cte_scan
SELECT series.id, (CURRENT_DATE - INTERVAL '500 days')::DATE
+ generate_series(1, series.id%500), repeat(' ', 10)
FROM generate_series(1, 5000) series (id);
CREATE INDEX idx_customer_id_date ON test_cte_scan
(customer_id, order_date DESC);
Now, we will demonstrate the CTE scan.
EXPLAIN ANALYZE WITH cte_test AS
(SELECT EXTRACT (year FROM order_date) as year, EXTRACT (month
FROM order_date) as month, COUNT(*) as total_customers
FROM test_cte_scan
GROUP BY year,month)
SELECT year, count(*) AS tab_data
FROM cte_test
GROUP BY year;
QUERY PLAN
------------------------------------------------------------------
HashAggregate (cost=37261.47..37263.47 rows=200 width=8) (actual
time=2807.585..2807.587 rows=2 loops=1)
CTE cte_test
-> HashAggregate (cost=37241.75..37249.14 rows=493
width=8) (actual time=2807.545..2807.551 rows=17 loops=1)
-> Seq Scan on test_cte_scan (cost=0.00..27885.50
rows=1247500 width=8) (actual time=0.037..1676.396
rows=1247500 loops=1)
-> CTE Scan on cte_test (cost=0.00..9.86 rows=493 width=8)
(actual time=2807.549..2807.571 rows=17 loops=1)
Total runtime: 2807.709 ms
(6 rows)
