postgresql - PL/pgSQL : Does DISTINCT ON passes through with it's result onto next CTE? -

my code looks (schema), because it's pretty huge:

something ( select distinct on (x1,x2,x3,x4) ... ), something2 (xx.*, ... xx left join ...), something3 (xx.*, ... something2 xx left join ...) select ... something3 

so imagine situation: after using distinct on (x1,x2,x3,x4) in something , then

select * 

(ignoring something2 , something3 here) : i 1700 results.

but the problem is not expected result (yet), because need use more cte leftjoin information

so when have same distinct on in something ,

select * something3 (which final expected result should return 1700 rows)

i 4000 results values wanted distinct earlier on in something.

it seems i'm losing distinct i've typed in something, because when put same syntax:

distinct on (x1,x2,x3,x4) ... in 3 something's 1700 results - it's not i'm looking for.

time means me.

could me out solution , better understanding of problem here?

this happens, because:

ctes can thought of defining temporary tables exist 1 query.

which means, doesn't matter, if defined 1 of cte distint on clause, because other ctes (and main query) see temporary table (or more result-set), nothing more. if use use join on temporary table, you end more results (just normal tables).

to ensure main query not contain duplicates, move distint on clause there (or, in theory, of ctes can have - @ least last 1 should have).

f.ex. these should produce desired output:

with s (select x t), s2 (select x s), s3 (select x s2), select distinct x s3;  -- vs.  s (select x t), s2 (select x s), s3 (select distinct x s2), select x s3;  -- vs.  s (select distinct x t), s2 (select distinct x s), s3 (select distinct x s2), select x s3;  -- last version should used, when have -- enormous joins, want keep "temporary" -- tables' size smaller in memory