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:
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),