From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Benjamin Smith <lists(at)benjamindsmith(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: converting in() clause into a with prefix? |
Date: | 2015-10-16 18:40:58 |
Message-ID: | CAKFQuwb5t=xag+KGtXEpFn=poxCGS=FTqOnPQzcSzaeYCgBJww@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
)
From | Date | Subject | |
---|---|---|---|
Next Message | dinesh kumar | 2015-10-16 19:06:54 | Re: converting in() clause into a with prefix? |
Previous Message | Benjamin Smith | 2015-10-16 18:18:14 | converting in() clause into a with prefix? |