Postgres Pro
Downloads
Home   >   mailing lists

Re: Most efficient way of querying M 'related' tables where N out of M may contain the key - Mailing list pgsql-performance

From David G. Johnston
Subject Re: Most efficient way of querying M 'related' tables where N out of M may contain the key
Date
Msg-id CAKFQuwY=xA3q4RuGwC5hFDUtQmquXWtXrtLR0ymsDk2POitUCA@mail.gmail.com
Whole thread Raw
In response to Most efficient way of querying M 'related' tables where N out of M may contain the key  (Stephane Bailliez <sbailliez@gmail.com>)
Responses Re: Most efficient way of querying M 'related' tables where N out of M may contain the key
List pgsql-performance
Tree view
On Thu, Aug 20, 2015 at 8:03 PM, Stephane Bailliez <sbailliez@gmail.com> wrote:
Pretty bad subject description... but let me try to explain. 


I'm trying to figure out what would be the most efficient way to query data from multiple tables using a foreign key.


​SELECT [...]
FROM (SELECT reference_id, [...] FROM table_where_referenced_id_is_a_pk WHERE reference_id EXISTS/IN/JOIN)​
 
​src
​LEFT JOIN type1 USING (reference_id)
LEFT JOIN type2 USING (reference_id)
[...]

Or consider whether PostgreSQL Inheritance would work - though basically its a friendly API over the "UNION ALL" query you proposed.

David J.

pgsql-performance by date:

Previous
From: Stephane Bailliez
Date:
Subject: Most efficient way of querying M 'related' tables where N out of M may contain the key
Next
From: Stephane Bailliez
Date:
Subject: Re: Most efficient way of querying M 'related' tables where N out of M may contain the key