So here is a really simple case of using CTE on Oracle.
create table test_table(
id int primary key,
foo varchar2(10)
);
insert into test_table(id, foo)
select rownum, 'val'||rownum from dual
connect by rownum<=100000;
explain plan for select * from (select * from test_table) sel where id=42;
select * from table(dbms_xplan.display);
--| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
--| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TABLE | 1 | 20 | 1 (0)| 00:00:01 |
--|* 2 | INDEX UNIQUE SCAN | SYS_C00328137 | 1 | | 1 (0)| 00:00:01 |
explain plan for with sel as (select * from test_table) select * from sel where id=42;
select * from table(dbms_xplan.display);
--| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
--| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TABLE | 1 | 20 | 1 (0)| 00:00:01 |
--|* 2 | INDEX UNIQUE SCAN | SYS_C00328137 | 1 | | 1 (0)| 00:00:01 |
You can see that from the resulting execution plan there is no difference between CTE and sub-select.
But here is similar sample on Postgres:
create table test_table(
id serial primary key,
foo text
);
insert into test_table(foo)
select 'val'||generate_series from generate_series(1, 100000);
explain analyze select * from (select * from test_table) as sel where id=42;
-- Index Scan using test_table_pkey on test_table (cost=0.29..8.31 rows=1 width=12) (actual time=0.037..0.038 rows=1 loops=1)
-- Index Cond: (id = 42)
-- Planning time: 0.053 ms
-- Execution time: 0.048 ms
explain analyze with sel as (select * from test_table) select * from sel where id=42;
-- CTE Scan on sel (cost=1541.00..3791.00 rows=500 width=36) (actual time=0.025..32.644 rows=1 loops=1)
-- Filter: (id = 42)
-- Rows Removed by Filter: 99999
-- CTE sel
-- -> Seq Scan on test_table (cost=0.00..1541.00 rows=100000 width=12) (actual time=0.016..5.859 rows=100000 loops=1)
-- Planning time: 0.069 ms
-- Execution time: 35.305 ms
Difference is dramatic. Quoting Tom Lane (real Postgres expert) "CTEs act as optimization fences. This is a feature, not a bug. Use them when you want to isolate the evaluation of a subquery." But a case when CTE should be used in Postgres is a theme for another post (maybe, so you here in another 4 or 5 years :-))