Re: converting in() clause into a with prefix?

On Fri, Oct 16, 2015 at 2:18 PM, Benjamin Smith <lists(at)benjamindsmith(dot)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
​
)
​