what about resolving the join "document doc JOIN document_usage du ON doc.id = du.document_id" using WITH? I think this won't work because the result of this CTE would be huge, but you could get the company with that id first using a WITH clause too, then you would have the name and you wouldn't have to join with the company table. 2014-04-16 5:11 GMT-03:00 Andreas Joseph Krogh <andreak@xxxxxxxxxxxx>: > På mandag 14. april 2014 kl. 21:55:26, skrev David G Johnston > <david.g.johnston@xxxxxxxxx>: > > A couple of thoughts: > > 1) The "union" really only pertains to the entity table rows; once you > "union all" those (duplicates should not matter and probably will not even > be present so using "all" avoids an unnecessary sort) you can join that > sub-query to the document_usage table. > > 2) Since every entity must have an associated company moving the company_id > field to the entity table will allow a direct search for company objects > using entity alone and remove the need to perform the union. The "company" > table omits the implicit self-referencing company_id but it is still there > in reality. > > David J. > > > > Thanks for having a look at it. > > I'm afraid I don't understand what you mean in 1); Can you give an example > of the query you're suggesting, joining "that subquery"? > > 2) Note that I need the names of the company owning each entity(person, > project etc.), and the details about those entities (name and ID), and in my > real app there are more entities which are not directly related to company, > but indirectly thru a project (ie. a task) and I want to be able to list > those entities' documents too. > > Do you have a suggestion for a better $subject for this kind of problem? > > thanks. > > -- > Andreas Joseph Krogh <andreak@xxxxxxxxxxxx> mob: +47 909 56 963 > Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no > Public key: http://home.officenet.no/~andreak/public_key.asc > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
