On Fri, Oct 16, 2015 at 2:18 PM, Benjamin Smith <lists@xxxxxxxxxxxxxxxxxx> 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
)
