pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfiltering after join
14+ messages / 4 participants
[nested] [flat]
* filtering after join
@ 2006-01-25 17:27 andrew <andrew.ylzhou@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: andrew @ 2006-01-25 17:27 UTC (permalink / raw)
To: pgsql-sql
I want to use a UDF to filter tuples t that are generated after a join.
More specifially, I have a UDF foo(record), which computes a value for
a given tuple. I can do the filtering before the join. e.g.:
select * from A, B where foo(A)<2 and A.a=B.b;
But I want to apply foo() to the tuples generated by the join
operation. How can I do that?
Thanks!
--
andrew
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: filtering after join
@ 2006-01-25 17:53 Michael Fuhr <mike@fuhr.org>
parent: andrew <andrew.ylzhou@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Michael Fuhr @ 2006-01-25 17:53 UTC (permalink / raw)
To: andrew <andrew.ylzhou@gmail.com>; +Cc: pgsql-sql
On Wed, Jan 25, 2006 at 06:27:33PM +0100, andrew wrote:
> I want to use a UDF to filter tuples t that are generated after a join.
> More specifially, I have a UDF foo(record), which computes a value for
> a given tuple. I can do the filtering before the join. e.g.:
>
> select * from A, B where foo(A)<2 and A.a=B.b;
>
> But I want to apply foo() to the tuples generated by the join
> operation. How can I do that?
Is this what you're looking for?
select *
from (select * from A, B where A.a = B.b) as s
where foo(s) < 2;
--
Michael Fuhr
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: filtering after join
@ 2006-01-25 19:11 Peter Eisentraut <peter_e@gmx.net>
parent: andrew <andrew.ylzhou@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Peter Eisentraut @ 2006-01-25 19:11 UTC (permalink / raw)
To: pgsql-sql; +Cc: andrew <andrew.ylzhou@gmail.com>
andrew wrote:
> I want to use a UDF to filter tuples t that are generated after a
> join. More specifially, I have a UDF foo(record), which computes a
> value for a given tuple. I can do the filtering before the join.
> e.g.:
>
> select * from A, B where foo(A)<2 and A.a=B.b;
What makes you think that the filtering happens before the join here?
And why would it matter? The results should be all the same.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: filtering after join
@ 2006-01-25 20:13 andrew <andrew.ylzhou@gmail.com>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 14+ messages in thread
From: andrew @ 2006-01-25 20:13 UTC (permalink / raw)
To: pgsql-sql
Sorry for the confusion. This is what i meant. Thanks, Michael.
select *
from (select * from A, B where A.a = B.b) as s
where foo(s) < 2;
On 1/25/06, Peter Eisentraut <peter_e@gmx.net> wrote:
> andrew wrote:
> > I want to use a UDF to filter tuples t that are generated after a
> > join. More specifially, I have a UDF foo(record), which computes a
> > value for a given tuple. I can do the filtering before the join.
> > e.g.:
> >
> > select * from A, B where foo(A)<2 and A.a=B.b;
>
> What makes you think that the filtering happens before the join here?
> And why would it matter? The results should be all the same.
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
>
--
andrew
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: filtering after join
@ 2006-01-26 10:27 Peter Eisentraut <peter_e@gmx.net>
parent: andrew <andrew.ylzhou@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Peter Eisentraut @ 2006-01-26 10:27 UTC (permalink / raw)
To: pgsql-sql; +Cc: andrew <andrew.ylzhou@gmail.com>
andrew wrote:
> Sorry for the confusion. This is what i meant. Thanks, Michael.
>
> select *
> from (select * from A, B where A.a = B.b) as s
> where foo(s) < 2;
>
> On 1/25/06, Peter Eisentraut <peter_e@gmx.net> wrote:
> > andrew wrote:
> > > I want to use a UDF to filter tuples t that are generated after a
> > > join. More specifially, I have a UDF foo(record), which computes
> > > a value for a given tuple. I can do the filtering before the
> > > join. e.g.:
> > >
> > > select * from A, B where foo(A)<2 and A.a=B.b;
Note that these two queries will produce the same internal execution
plan, so if you wanted to make some difference you will not.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: filtering after join
@ 2006-01-26 10:54 andrew <andrew.ylzhou@gmail.com>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 14+ messages in thread
From: andrew @ 2006-01-26 10:54 UTC (permalink / raw)
To: pgsql-sql
But the function foo() would produce different values for the two
queries, so the result will be different.
A simple example is foo() computes the sum of all the integer fields
of the input record.
On 1/26/06, Peter Eisentraut <peter_e@gmx.net> wrote:
> andrew wrote:
> > Sorry for the confusion. This is what i meant. Thanks, Michael.
> >
> > select *
> > from (select * from A, B where A.a = B.b) as s
> > where foo(s) < 2;
> >
> > On 1/25/06, Peter Eisentraut <peter_e@gmx.net> wrote:
> > > andrew wrote:
> > > > I want to use a UDF to filter tuples t that are generated after a
> > > > join. More specifially, I have a UDF foo(record), which computes
> > > > a value for a given tuple. I can do the filtering before the
> > > > join. e.g.:
> > > >
> > > > select * from A, B where foo(A)<2 and A.a=B.b;
>
> Note that these two queries will produce the same internal execution
> plan, so if you wanted to make some difference you will not.
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
>
--
andrew
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: filtering after join
@ 2006-01-26 11:22 Peter Eisentraut <peter_e@gmx.net>
parent: andrew <andrew.ylzhou@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Peter Eisentraut @ 2006-01-26 11:22 UTC (permalink / raw)
To: pgsql-sql; +Cc: andrew <andrew.ylzhou@gmail.com>
andrew wrote:
> But the function foo() would produce different values for the two
> queries, so the result will be different.
> A simple example is foo() computes the sum of all the integer fields
> of the input record.
OK, I see now where you're getting at. You want to combine the record
type of A and B into one. Then the proposed solution is right.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: filtering after join
@ 2006-01-26 12:54 andrew <andrew.ylzhou@gmail.com>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 14+ messages in thread
From: andrew @ 2006-01-26 12:54 UTC (permalink / raw)
To: pgsql-sql
How will the query planner do for a nesting query? Treat the
subqueries as multiple queries and then link them together?
where can I find the information (codes or documents)?
Thanks.
On 1/26/06, Peter Eisentraut <peter_e@gmx.net> wrote:
> andrew wrote:
> > But the function foo() would produce different values for the two
> > queries, so the result will be different.
> > A simple example is foo() computes the sum of all the integer fields
> > of the input record.
>
> OK, I see now where you're getting at. You want to combine the record
> type of A and B into one. Then the proposed solution is right.
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
>
--
andrew
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: filtering after join
@ 2006-01-26 16:59 Peter Eisentraut <peter_e@gmx.net>
parent: andrew <andrew.ylzhou@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Peter Eisentraut @ 2006-01-26 16:59 UTC (permalink / raw)
To: pgsql-sql; +Cc: andrew <andrew.ylzhou@gmail.com>
andrew wrote:
> How will the query planner do for a nesting query? Treat the
> subqueries as multiple queries and then link them together?
> where can I find the information (codes or documents)?
Look at the execution plan using the EXPLAIN command.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: filtering after join
@ 2006-01-27 09:10 andrew <andrew.ylzhou@gmail.com>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 14+ messages in thread
From: andrew @ 2006-01-27 09:10 UTC (permalink / raw)
To: pgsql-sql
I can see the final plan by using the EXPLAIN command. But I want to
know the procedure of the planner in handling nesting queries. Can you
direct me to the corresponding part of the code and/or the documents?
Thanks.
On 1/26/06, Peter Eisentraut <peter_e@gmx.net> wrote:
> andrew wrote:
> > How will the query planner do for a nesting query? Treat the
> > subqueries as multiple queries and then link them together?
> > where can I find the information (codes or documents)?
>
> Look at the execution plan using the EXPLAIN command.
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
>
--
andrew
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: filtering after join
@ 2006-01-27 09:17 andrew <andrew.ylzhou@gmail.com>
parent: andrew <andrew.ylzhou@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: andrew @ 2006-01-27 09:17 UTC (permalink / raw)
To: pgsql-sql
I got errors in this query. I have a function complete(record) which
takes a generic record type data. But it seems cannot be applied to a
sub-select result:
backend> explain select * from (select * from Person,Auction where
Person.id=Auction.seller) as s where complete(s)
QUERY: explain select * from (select * from Person,Auction where
Person.id=Auction.seller) as s where complete(s)
ERROR: Cannot pass result of sub-select or join s to a function
On 1/27/06, andrew <andrew.ylzhou@gmail.com> wrote:
> I can see the final plan by using the EXPLAIN command. But I want to
> know the procedure of the planner in handling nesting queries. Can you
> direct me to the corresponding part of the code and/or the documents?
> Thanks.
>
> On 1/26/06, Peter Eisentraut <peter_e@gmx.net> wrote:
> > andrew wrote:
> > > How will the query planner do for a nesting query? Treat the
> > > subqueries as multiple queries and then link them together?
> > > where can I find the information (codes or documents)?
> >
> > Look at the execution plan using the EXPLAIN command.
> >
> > --
> > Peter Eisentraut
> > http://developer.postgresql.org/~petere/
> >
>
>
> --
> andrew
>
--
andrew
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: filtering after join
@ 2006-01-27 14:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: andrew <andrew.ylzhou@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: Tom Lane @ 2006-01-27 14:55 UTC (permalink / raw)
To: andrew <andrew.ylzhou@gmail.com>; +Cc: pgsql-sql
andrew <andrew.ylzhou@gmail.com> writes:
> I got errors in this query. I have a function complete(record) which
> takes a generic record type data. But it seems cannot be applied to a
> sub-select result:
As I seem to recall having mentioned several times already, PG 7.3 is
really, really weak in this area. (If you ever got it to work, which
I doubt you will, you'd probably then start to notice how badly it
leaks memory for whole-row operations...)
You should either forget this idea or invest the effort to move up
to PG 8.1.
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: filtering after join
@ 2006-01-27 15:21 andrew <andrew.ylzhou@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 14+ messages in thread
From: andrew @ 2006-01-27 15:21 UTC (permalink / raw)
To: pgsql-sql
Sorry. I am working on a research prototype built on 7.3 which contains
a cosiderable amount of efforts. The original developers of the prototype
have no interest in upgrading it. It is also impossible for me to upgrade it
by myself. Could you give me any tips to sovle the current problem? Thanks!
On 1/27/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> andrew <andrew.ylzhou@gmail.com> writes:
> > I got errors in this query. I have a function complete(record) which
> > takes a generic record type data. But it seems cannot be applied to a
> > sub-select result:
>
> As I seem to recall having mentioned several times already, PG 7.3 is
> really, really weak in this area. (If you ever got it to work, which
> I doubt you will, you'd probably then start to notice how badly it
> leaks memory for whole-row operations...)
>
> You should either forget this idea or invest the effort to move up
> to PG 8.1.
>
> regards, tom lane
>
--
andrew
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: filtering after join
@ 2006-01-27 16:32 andrew <andrew.ylzhou@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 14+ messages in thread
From: andrew @ 2006-01-27 16:32 UTC (permalink / raw)
To: pgsql-sql
I have traced the code. It exits when the argument is the result of a
join or a subselect in function ParseFuncOrColumn(). The reason
mentioned in the comments is lack of named tuple type. How can force
it to create such a tuple type? is there a way? thanks a million
times!
On 1/27/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> andrew <andrew.ylzhou@gmail.com> writes:
> > I got errors in this query. I have a function complete(record) which
> > takes a generic record type data. But it seems cannot be applied to a
> > sub-select result:
>
> As I seem to recall having mentioned several times already, PG 7.3 is
> really, really weak in this area. (If you ever got it to work, which
> I doubt you will, you'd probably then start to notice how badly it
> leaks memory for whole-row operations...)
>
> You should either forget this idea or invest the effort to move up
> to PG 8.1.
>
> regards, tom lane
>
--
andrew
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2006-01-27 16:32 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-25 17:27 filtering after join andrew <andrew.ylzhou@gmail.com>
2006-01-25 17:53 ` Michael Fuhr <mike@fuhr.org>
2006-01-25 19:11 ` Peter Eisentraut <peter_e@gmx.net>
2006-01-25 20:13 ` andrew <andrew.ylzhou@gmail.com>
2006-01-26 10:27 ` Peter Eisentraut <peter_e@gmx.net>
2006-01-26 10:54 ` andrew <andrew.ylzhou@gmail.com>
2006-01-26 11:22 ` Peter Eisentraut <peter_e@gmx.net>
2006-01-26 12:54 ` andrew <andrew.ylzhou@gmail.com>
2006-01-26 16:59 ` Peter Eisentraut <peter_e@gmx.net>
2006-01-27 09:10 ` andrew <andrew.ylzhou@gmail.com>
2006-01-27 09:17 ` andrew <andrew.ylzhou@gmail.com>
2006-01-27 14:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-27 15:21 ` andrew <andrew.ylzhou@gmail.com>
2006-01-27 16:32 ` andrew <andrew.ylzhou@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox