Search Postgresql Archives

Re: Handling large number of OR/IN conditions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On May 1, 2009, at 10:49 AM, David Wall wrote:

We have a database report function that seemed clean when the number of users was small, but as the number of users grow, I was wondering if anybody had any good ideas about how to handle OR or IN for SELECTs.

The general scenario is that a manager runs reports that list all records that were created by users under his/her oversight. So, when the number of users is small, we had simple queries like:

SELECT field1, field2 FROM table1 WHERE creator_user_id = 'U1' OR creator_user_id = 'U2';

But when there are thousands of users, and a manager has oversight of 100 of them, the OR construct seems out of whack when you read the query:

WHERE creator_user_id = 'U1' OR creator_user_id = 'U2' ... OR creator_user_id = 'U99' OR creator_user_id = 'U100'

I know it can be shortened with IN using something like, but don't know if it's any more/less efficient or a concern:

WHERE creator_user_id IN ('U1', 'U2', ...., 'U99', 'U100)

How do people tend to handle this sort of thing? I suspect manager reports against their people must be pretty common. Are there any good tricks on how to group users like this? Unfortunately, group membership changes over time, and users may report to more than one manager and thus belong to more than one group, so we can't just have a 'creator_group_id' attribute that is set and then query against that.


Sounds like a job for a two column table that lists manager and report.

select table1.field1, table2.field2 from table1, reports where table1.creator_user_id = reports.peon and reports.overlord = 'bob'

Cheers,
  Steve


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux