On Fri, Oct 16, 2015 at 11:18 AM, 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
)" ;
Ignore this approach, if you have already tried this as below.
PREPARE stmt(record[]) AS WITH incla AS (
SELECT * FROM (SELECT UNNEST(ARRAY[$1])) f
)
SELECT * FROM incla WHERE (1,2) IN (unnest);
EXECUTE stmt(ARRAY[(1,2), (2,1)]);
// 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),
(291,191),(291,195),(291,198),(291,197),(291,188),(291,184),(291,199),
(291,193),(291,185),(291,194),(291,-2),(291,186),(291,183),(291,187),
(291,200),(291,190),(291,196),(291,202),(200,455),(200,433),(200,442),
(200,440),(200,445),(200,438),(200,437),(200,450),(200,452),(200,459),
(200,456),(200,432),(200,441),(200,460),(200,446),(200,447),(200,448),
(200,449),(200,434),(200,443),(200,-2),(200,444),(200,457),(200,458),
(200,451),(200,454),(200,453),(246,-1),(246,190),(246,186),(246,188),
(246,195),(246,196),(246,197),(246,192),(246,-2),(246,184),(246,189),
(246,193),(246,194),(246,198),(246,191),(246,199),(246,187),(246,185),
(246,183),(246,200),(63,-1),(63,269),(63,267),(63,264),(63,258),(63,126),
(63,270),(63,262),(63,127),(63,134),(63,263),(63,271),(63,272),(63,265),
(63,-2),(63,259),(63,260),(63,266),(9,-1),(9,283),(9,192),(9,266),(9,276),
(9,313),(9,294),(9,311),(9,298),(9,299),(9,300),(9,312),(9,310),(9,263),
(9,272),(9,305),(9,314),(9,152),(9,284),(9,277),(9,260),(9,271),(9,278),
(9,315),(9,287),(9,289),(9,316),(9,288),(9,290),(9,259),(9,258),(9,269),
(9,279),(9,265),(9,273),(9,280),(9,64),(9,264),(9,281),(9,317),(9,304),
(9,303),(9,322),(9,267),(9,282),(9,318),(9,306),(9,295),(9,319),(9,301),
(9,302),(9,43),(9,268),(9,275),(9,-2),(9,262),(9,296),(9,297),(9,320),(9,286),
(9,274),(9,307),(9,308),(9,321),(9,270),(9,261),(9,309),(9,292),(9,293),
(9,285),(9,291),(94,-1),(94,280),(94,285),(94,268),(94,265),(94,278),(94,283),
(94,287),(94,288),(94,289),(94,284),(94,281),(94,110),(94,262),(94,259),
(94,273),(94,270),(94,266),(94,258),(94,269),(94,274),(94,263),(94,290),
(94,272),(94,264),(94,260),(94,279),(94,261),(94,286),(94,275),(94,277),
(94,291),(94,109),(94,-2),(94,257),(111,-1),(111,452),(111,486),(111,404),
(111,456),(111,455),(111,485),(111,484),(111,483),(111,457),(111,441),
(111,468),(111,430),(111,442),(111,446),(111,490),(111,440),(111,466),
(111,491),(111,449),(111,454),(111,461),(111,492),(111,469),(111,465),
(111,458),(111,426),(111,443),(111,493),(111,488),(111,475),(111,481),
(111,482),(111,474),(111,477),(111,478),(111,476),(111,479),(111,480),
(111,471),(111,463),(111,450),(111,470),(111,494),(111,447),(111,448),
(111,495),(111,-2),(111,473),(111,496),(111,429),(111,487),(111,497),
(111,167),(111,444),(111,451),(111,431),(111,445),(111,166),(111,453),
(111,467),(111,462),(111,464),(111,472),(111,489),(334,-1),(334,188),
(334,189),(334,203),(334,201),(334,200),(334,193),(334,192),(334,191),
(334,202),(334,185),(334,190),(334,204),(334,186),(334,187),(334,197),
(334,199),(334,205),(334,184),(334,194),(334,-2),(334,196),(334,183),
(334,195),(334,206),(334,198),(334,207),(201,-1),(201,445),(201,442),
(201,446),(201,468),(201,458),(201,455),(201,456),(201,469),(201,454),
(201,453),(201,466),(201,474),(201,443),(201,441),(201,440),(201,447),
(201,470),(201,460),(201,459),(201,461),(201,471),(201,465),(201,463),
(201,448),(201,451),(201,472),(201,457),(201,-2),(201,462),(201,464),
(201,452),(201,449),(201,450),(201,473),(201,467),(201,475),(62,-1),(62,279),
(62,280),(62,294),(62,281),(62,282),(62,285),(62,274),(62,299),(62,300),
(62,290),(62,291),(62,289),(62,273),(62,286),(62,194),(62,295),(62,275),
(62,-2),(62,292),(62,301),(62,196),(62,195),(62,296),(62,276),(62,284),
(62,287),(62,297),(62,288),(62,277),(62,298),(62,278),(188,-1),(188,443),
(188,446),(188,449),(188,453),(188,454),(188,455),(188,456),(188,450),
(188,445),(188,448),(188,451),(188,447),(188,441),(188,-2),(188,440),
(188,442),(188,444),(188,452),(405,-1),(405,187),(405,188),(405,183),
(405,184),(405,186),(405,189),(405,190),(405,185),(405,-2),(415,-1),(415,190),
(415,195),(415,183),(415,185),(415,192),(415,187),(415,-2),(415,188),
(415,184),(415,186),(415,189),(415,193),(415,191),(415,196),(290,-1),
(290,185),(290,194),(290,206),(290,190),(290,183),(290,193),(290,207),
(290,200),(290,208),(290,191),(290,205),(290,209),(290,184),(290,202),
(290,203),(290,198),(290,210),(290,201),(290,211),(290,189),(290,195),
(290,204),(290,199),(290,212),(290,186),(290,196),(290,213),(290,-2),
(290,188),(290,187),(290,197),(290,214),(469,-1),(469,183),(469,-2),(465,-1),
(465,184),(465,-2),(465,183),(316,-1),(316,-2));
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
