0

I have a question related to behavior of WITH queries.

So I have

WITH test as (
    SELECT id, name, ....
    FROM test...
    WHERE ... )

In that auxiliary statement I select a column(s) that have index on them. So question is - If i select later on from test will it use that index?

2

Short answer

No

Long Answer

The WITH clause, also known as CTE ( Common Table Expressions ) is evaluated first. If there are multiple CTE's the are evaluated in order of the appearance.

The resultset is kept in something like an "in memory temporary table" without any index on it.

Unless there is a very good reason, i would suggest to avoid CTE's as they are optimization fences ( for the query planner).

2
  • Thank you for the answer, it is really hepful. I knew almost everything already except the The resultset is kept in something like an "in memory temporary table" without any index on it. part. Is there any documentation (for any RDB out there) that explains this further? If there is - I would really appreciate you pasting the url here :)
    – Rouz
    Jul 26 '18 at 13:27
  • 1
    @Rouz you should read this article and reference to PG source code: dba.stackexchange.com/questions/91136/….
    – Luan Huynh
    Jul 26 '18 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.