Обсуждение: reuse a subquery
I have a query that uses the same subquery twice. What is the correct syntax to reuse the subquery instead of running it twice? The query below 'works' but reruns the identical subquery. The point of the subquery is to limit the join work to the subset of table_a where c = 3 instead of the entire table_a with c ranging from 0 to 65535. The planner helps expedite the rerun query, but there must be a better syntax for subquery reuse.
E.g.
SELECT *
FROM
(
SELECT *
FROM table_a
WHERE c = 3
ORDER BY d
) AS T1
LEFT JOIN
(
SELECT *
FROM table_a
WHERE c = 3
ORDER BY d
) AS T2
ON T2.d = (T1.d + 5)
WHERE T2.d IS NULL
ORDER BY T1.d;
E.g.
SELECT *
FROM
(
SELECT *
FROM table_a
WHERE c = 3
ORDER BY d
) AS T1
LEFT JOIN
(
SELECT *
FROM table_a
WHERE c = 3
ORDER BY d
) AS T2
ON T2.d = (T1.d + 5)
WHERE T2.d IS NULL
ORDER BY T1.d;
On 9 August 2010 15:04, Charles Holleran <scorpdaddy@hotmail.com> wrote: > I have a query that uses the same subquery twice. What is the correct > syntax to reuse the subquery instead of running it twice? The query below > 'works' but reruns the identical subquery. The point of the subquery is to > limit the join work to the subset of table_a where c = 3 instead of the > entire table_a with c ranging from 0 to 65535. The planner helps expedite > the rerun query, but there must be a better syntax for subquery reuse. > > E.g. > > SELECT * > > FROM > ( > SELECT * > FROM table_a > WHERE c = 3 > ORDER BY d > ) AS T1 > > LEFT JOIN > > ( > SELECT * > FROM table_a > WHERE c = 3 > ORDER BY d > ) AS T2 > > ON T2.d = (T1.d + 5) > WHERE T2.d IS NULL > ORDER BY T1.d; > Can't you just do: SELECT * FROM table_a WHERE d+5 NOT IN (SELECT t1.d FROM table_a AS t1) ORDER BY d; -- Thom Brown Registered Linux user: #516935
> From: thom@linux.com
> Date: Mon, 9 Aug 2010 15:12:51 +0100
> Subject: Re: [NOVICE] reuse a subquery
> To: scorpdaddy@hotmail.com
> CC: pgsql-novice@postgresql.org
>
> On 9 August 2010 15:04, Charles Holleran <scorpdaddy@hotmail.com> wrote:
> > I have a query that uses the same subquery twice. What is the correct
> > syntax to reuse the subquery instead of running it twice? The query below
> > 'works' but reruns the identical subquery. The point of the subquery is to
> > limit the join work to the subset of table_a where c = 3 instead of the
> > entire table_a with c ranging from 0 to 65535. The planner helps expedite
> > the rerun query, but there must be a better syntax for subquery reuse.
> >
> > E.g.
> >
> > SELECT *
> >
> > FROM
> > (
> > SELECT *
> > FROM table_a
> > WHERE c = 3
> > ORDER BY d
> > ) AS T1
> >
> > LEFT JOIN
> >
> > (
> > SELECT *
> > FROM table_a
> > WHERE c = 3
> > ORDER BY d
> > ) AS T2
> >
> > ON T2.d = (T1.d + 5)
> > WHERE T2.d IS NULL
> > ORDER BY T1.d;
> >
>
> Can't you just do:
>
> SELECT *
> FROM table_a
> WHERE d+5 NOT IN (SELECT t1.d FROM table_a AS t1)
> ORDER BY d;
>
> --
> Thom Brown
> Registered Linux user: #516935
>
The intent was to not run 2 selects from table_a. If the subquery is reused, then only the one subquery selects from table_a, limiting the work thereafter to the limited subset of table_a where c = 3. The second syntax suggested 'works' but also runs the select from table_a twice.
On 9 August 2010 15:34, Charles Holleran <scorpdaddy@hotmail.com> wrote: > > >> From: thom@linux.com >> Date: Mon, 9 Aug 2010 15:12:51 +0100 >> Subject: Re: [NOVICE] reuse a subquery >> To: scorpdaddy@hotmail.com >> CC: pgsql-novice@postgresql.org >> >> On 9 August 2010 15:04, Charles Holleran <scorpdaddy@hotmail.com> wrote: >> > I have a query that uses the same subquery twice. What is the correct >> > syntax to reuse the subquery instead of running it twice? The query >> > below >> > 'works' but reruns the identical subquery. The point of the subquery is >> > to >> > limit the join work to the subset of table_a where c = 3 instead of the >> > entire table_a with c ranging from 0 to 65535. The planner helps >> > expedite >> > the rerun query, but there must be a better syntax for subquery reuse. >> > >> > E.g. >> > >> > SELECT * >> > >> > FROM >> > ( >> > SELECT * >> > FROM table_a >> > WHERE c = 3 >> > ORDER BY d >> > ) AS T1 >> > >> > LEFT JOIN >> > >> > ( >> > SELECT * >> > FROM table_a >> > WHERE c = 3 >> > ORDER BY d >> > ) AS T2 >> > >> > ON T2.d = (T1.d + 5) >> > WHERE T2.d IS NULL >> > ORDER BY T1.d; >> > >> >> Can't you just do: >> >> SELECT * >> FROM table_a >> WHERE d+5 NOT IN (SELECT t1.d FROM table_a AS t1) >> ORDER BY d; >> >> -- >> Thom Brown >> Registered Linux user: #516935 >> > > The intent was to not run 2 selects from table_a. If the subquery is > reused, then only the one subquery selects from table_a, limiting the work > thereafter to the limited subset of table_a where c = 3. The second syntax > suggested 'works' but also runs the select from table_a twice. > The problem is that you're treating table_a as a separate materialized table by transposing all the d values by 5, so I'm not quite sure how you can avoid using the table twice. -- Thom Brown Registered Linux user: #516935
Charles Holleran wrote on 09.08.2010 16:04:
> I have a query that uses the same subquery twice. What is the correct
> syntax to reuse the subquery instead of running it twice? The query
> below 'works' but reruns the identical subquery. The point of the
> subquery is to limit the join work to the subset of table_a where c = 3
> instead of the entire table_a with c ranging from 0 to 65535. The
> planner helps expedite the rerun query, but there must be a better
> syntax for subquery reuse.
>
> E.g.
>
> SELECT *
>
> FROM
> (
> SELECT *
> FROM table_a
> WHERE c = 3
> ORDER BY d
> ) AS T1
>
> LEFT JOIN
>
> (
> SELECT *
> FROM table_a
> WHERE c = 3
> ORDER BY d
> ) AS T2
>
> ON T2.d = (T1.d + 5)
> WHERE T2.d IS NULL
> ORDER BY T1.d;
>
What about:
WITH temp_a (col1, col2, col3) AS
(
SELECT col1, col2, col3
FROM table_a
WHERE c = 3
)
SELECT *
FROM temp_a t1
JOIN temp_a t2 ON (t2.d = t1.d + 5)
WHERE t2.d IS NULL
ORDER BY t1.d;
Regards
Thomas
You mean a query with just one SELECT clause?
SELECT *
FROM table_a primeiro
LEFT JOIN table_a segundo
ON primeiro.d + 5 = segundo.d
WHERE primeiro.c= 3
AND segundo.c = 3
AND segundo.d IS NULL
ORDER BY d
Best,
Oliveiros
----- Original Message -----From: Charles HolleranSent: Monday, August 09, 2010 3:34 PMSubject: Re: [NOVICE] reuse a subquery
> From: thom@linux.com
> Date: Mon, 9 Aug 2010 15:12:51 +0100
> Subject: Re: [NOVICE] reuse a subquery
> To: scorpdaddy@hotmail.com
> CC: pgsql-novice@postgresql.org
>
> On 9 August 2010 15:04, Charles Holleran <scorpdaddy@hotmail.com> wrote:
> > I have a query that uses the same subquery twice. What is the correct
> > syntax to reuse the subquery instead of running it twice? The query below
> > 'works' but reruns the identical subquery. The point of the subquery is to
> > limit the join work to the subset of table_a where c = 3 instead of the
> > entire table_a with c ranging from 0 to 65535. The planner helps expedite
> > the rerun query, but there must be a better syntax for subquery reuse.
> >
> > E.g.
> >
> > SELECT *
> >
> > FROM
> > (
> > SELECT *
> > FROM table_a
> > WHERE c = 3
> > ORDER BY d
> > ) AS T1
> >
> > LEFT JOIN
> >
> > (
> > SELECT *
> > FROM table_a
> > WHERE c = 3
> > ORDER BY d
> > ) AS T2
> >
> > ON T2.d = (T1.d + 5)
> > WHERE T2.d IS NULL
> > ORDER BY T1.d;
> >
>
> Can't you just do:
>
> SELECT *
> FROM table_a
> WHERE d+5 NOT IN (SELECT t1.d FROM table_a AS t1)
> ORDER BY d;
>
> --
> Thom Brown
> Registered Linux user: #516935
>
The intent was to not run 2 selects from table_a. If the subquery is reused, then only the one subquery selects from table_a, limiting the work thereafter to the limited subset of table_a where c = 3. The second syntax suggested 'works' but also runs the select from table_a twice.