Combine CTE along with an IN, in PostgreSQL

Question

So I have this simple query in PostgreSQL 10.

with bunch_of_things as (
    select vans_id from shoes where adidas_id = 1
)

select * from vans where vans.id in (bunch_of_things) ;

I am getting an error column "bunch_of_things" does not exist

I know I could put the first select inside the parentheses of the second query, to define the IN part

But since I will use the results of the first query multiple times in the same transaction, I dont want to do the same query multiple times.

So how can I make the IN work along with a CTE?

(And if this is not possible, how can I get the results of a query once and use them multiple times in a transaction?)

Thanks

Answer 1

Name of the CTE is like a table so you should do a select

with bunch_of_things as (
    select vans_id from shoes where adidas_id = 1
)

select * from vans where vans.id in (select vans_id from bunch_of_things);

There are several things however you need to take into account.

Firstly, EXISTS usually is better performance wise than IN

with bunch_of_things as (
    select vans_id from shoes where adidas_id = 1
) 
select * 
  from vans v
where EXISTS (
    select 1
    from bunch_of_things b
    where b.vans_id = v.id
)

Secondly, in postgres 10 and below CTE is a performance fence so postgres cannot optimize the query as a whole (this might change however). This may be helpful way to control the query execution in some cases and is definitely the thing you should take into consideration.

The alternative way to execute a query and reuse the results in a transaction is using temporary tables like this:

CREATE TEMPORARY TABLE bunch_of_things (vans_id integer)
ON COMMIT DROP;

INSERT INTO bunch_of_things (vans_id)
SELECT vans_id FROM shoes where adidas_id = 1;

And then use the table as usual:

select * 
  from vans v
where EXISTS (
    select 1
    from bunch_of_things b
    where b.vans_id = v.id
)

Answer 2

This will always be slow because in PostgreSQL a CTE is a optimization fence.

You'll want something like this,

SELECT *
FROM vans
WHERE EXISTS (
    SELECT
    FROM shoes
    WHERE adidas_id = 1
      AND vans_id = vans.id
)

that will be much faster.