On May 1, 2009, at 2:42 PM, David Wall wrote:
(quoted from Chris)
Select field1,field2 FROM table1 inner join relationships on
table1.creator_user_id = relationships.employee WHERE
relationships.manager = ?
(quoted from Steve)
select table1.field1, table2.field2 from table1, reports where
table1.creator_user_id = reports.peon and reports.overlord = 'bob'
Thanks, Steve and Chris, who both suggested similar things.
I believe that will work for me in some situations, but currently
the report allows the manager to select any combination (from one to
all) of users that report to him. So one solution would be to run
the report as you have it and then have the application filter it,
but that could be painful if the users "de-selected" from his full
team happen to have a large percentage of the matching rows. Of
course, I may see if this is something they really do much of. I
mean, perhaps if they select individual users (just a few), I can
just use the OR/IN style, and if they select "all my users" I could
rely on the table joins.
Does anybody know if PG will perform better with the table join
instead of evaluating the series of OR/IN? The OR/IN has to be
parsed, but the comparisons may be faster than the table join.
It used to be that populating and then joining with a temporary table
was faster than using IN (1, 3, 5, 7, 9, 11, 13) for all but the
smallest sets. That's no longer true, and IN() is pretty good.
I'd still use a temporary table myself, though. It's cleaner and
easier to populate one than to cleanly produce a statement with a
variable number of identifiers in it. And you can reuse it for
multiple reports, join against it different ways and so on. Also you
can populate it either from your UI or by selecting from the
relationships table suggested above (create temporary table foo as
select peon from reports where overlord in ('bob', 'ben', 'jerry) ),
and still run the same reports against it.
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
