How PostgreSQL execute query? -

can explain why postgresql works so:

if execute query

select * project_archive_doc pad, project_archive_doc pad2 pad.id = pad2.id 

it simple join , explain looks this:

hash join  (cost=6.85..13.91 rows=171 width=150)   hash cond: (pad.id = pad2.id)   ->  seq scan on project_archive_doc pad  (cost=0.00..4.71 rows=171 width=75)   ->  hash  (cost=4.71..4.71 rows=171 width=75)         ->  seq scan on project_archive_doc pad2  (cost=0.00..4.71 rows=171 width=75) 

but if execute query:

select * project_archive_doc pad pad.id = (           select pad2.id           project_archive_doc pad2                     pad2.project_id = pad.project_id           order pad2.created_at           limit 1) 

there no joins , explain looks like:

seq scan on project_archive_doc pad  (cost=0.00..886.22 rows=1 width=75)"   filter: (id = (subplan 1))   subplan 1     ->  limit  (cost=5.15..5.15 rows=1 width=8)           ->  sort  (cost=5.15..5.15 rows=1 width=8)                 sort key: pad2.created_at                 ->  seq scan on project_archive_doc pad2  (cost=0.00..5.14 rows=1 width=8)                       filter: (project_id = pad.project_id) 

why , there documentation or articles this?

without table definitions , data it's hard specific case. in general, postgresql sql databases in doesn't treat sql step-by-step program how execute query. it's more description of want results , hint how want database produce results.

postgresql free execute query can efficiently so, long produces results want.

often has several choices how produce particular result. choose between them based on cost estimates.

it can "understand" several different ways of writing particular query equivalent, , transform 1 it's more efficient. example, can transform in (select ...) join, because can prove they're equivalent.

however, apparently small changes query fundamentally change meaning, , limit optimisations/transformations postgresql can make. adding limit or offset inside subquery prevents postgresql flattening it, i.e. combining outer query tranforming join. prevents postgresql moving where clause entries between subquery , outer query, because that'd change meaning of query. without limit or offset clause, can both these things because don't change query's meaning.

there's some info on planner here.