On Friday 25 February 2005 07:46 pm, Andrew Perrin wrote: > Actually it does work slightly modified - here's the query that works for > my application: > > leted=# Select s.partid, s.zip, c.coder, c.ca_date, c.tone_angry > leted-# from participants s,code_assignments c > leted-# where c.ca_partid = s.partid > leted-# and c.ca_date = (select min(c2.ca_date) from code_assignments c2 > where c.ca_partid=c2.ca_partid) > leted-# ;
I just thought that I'd point out that your query has a nested subquery (the
select on the right of that equals in your where clause). I'm not sure how
postgreSQL actually handles this query, but I know that Oracle executes this
type of query by running the correlated subquery for each row in the
code_assignments (c) table. For large tables (millions of rows), performance
of this type of query can really suck.
I would normally try to replace the correlated subquery with a view or an
inline view. So, for example (untested code follows)
select
s.partid, s.zip, c.coder, c.ca_date, c.tone_angry
from
participants s,
code_assignments c,
� � (select min(c2.ca_date) min_date, c2.ca_partid
from
code_assignments c2
group by
c2.ca_partid) d
where d.ca_partid = c.ca_partid
and c.ca_partid = s.partid
and c.ca_date = d.min_date
Since the nested select appears here in the from clause, it can be executed
once instead of once per row in the other table. I'm pretty sure that
postgreSQL supports this type of subquery. If not, you can create a view and
use that view for the "d" table in this query. In either case, a query
structured in this way is much easier for the RDBMS to optimize.
---Tom
pgpQj8V1VVp15.pgp
Description: PGP signature
-- TriLUG mailing list : http://www.trilug.org/mailman/listinfo/trilug TriLUG Organizational FAQ : http://trilug.org/faq/ TriLUG Member Services FAQ : http://members.trilug.org/services_faq/ TriLUG PGP Keyring : http://trilug.org/~chrish/trilug.asc
