Re: converting in() clause into a with prefix? - Mailing list pgsql-general

I have a horribly-performing query similar to below, and I'd like to convert
it to use a "WITH mytable as ( ... ) " without having to re-architect my code.
For some reason, using a WITH prefix seems to generally work much faster than
IN() sub clause even allowing identical results. (runs in 1/4th the time)

Is there a PG native function that can convert the listing format of in()
clause to row-level results from a WITH prefix? I see the array* functions but
they seem to work with arrays like "array[1,2,3]" and unnest seems to drill
right through nested arrays and flattens every single element to a new row,
regardless of depth.  EG: the following two lines are equivalent:

select unnest(array([1,2,2,3]);
select unnest(array[array[1,2],array[2,3]]);

I'd expect the latter to put out two rows as

1, 2
2, 3

Thanks for your input, clarifying pseudo code examples below (PHP). We're
running 9.4.4 on CentOS 6.

Ben


// DESIRED END RESULT PSUEDO CODE
$query = "
WITH mytable AS
        (
        unnest(". $in .", school_id, building_id)
        )
SELECT
        id,
        name
FROM mytable
JOIN classes ON
        (
        mytable.school_id = classes.school_id
        AND mytable.building_id = classes.building_id
        )" ;


// CURRENT CODE EXAMPLE (PHP)
$query = "
SELECT
        id,
        name
FROM classes
WHERE
        (classes.school_id, classes.building_id) IN (" . $in . ")";


// EXAMPLE RESULT (small list)
SELECT
        id,
        name
FROM classes
WHERE
        (classes.school_id, classes.building_id) IN ((291,189),(291,192),

​[...]​