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

On Fri, Oct 16, 2015 at 11:18 AM, 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
> )" ;
>
>
>
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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--

Regards,
Dinesh
manojadinesh.blogspot.com