On Fri, Oct 16, 2015 at 2:18 PM, Benjamin Smith <li...@benjamindsmith.com> wrote:
> 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), > [...] > > > WITH input_table AS ( SELECT split_part(input_as_table, ',', 1) AS col1, split_part(input_as_table, ',', 2) AS col2 FROM regexp_split_to_table( substring('(1,2),(3,4)', 2, 11-2), -- 11 = length of input string '\),\(' ) AS input_as_table )