Re: Most efficient way of querying M 'related' tables where N out of M may contain the key

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: Stephane Bailliez <sbailliez(at)gmail(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Most efficient way of querying M 'related' tables where N out of M may contain the key
Date: 2015-08-21 00:19:22
Message-ID: CAKFQuwY=xA3q4RuGwC5hFDUtQmquXWtXrtLR0ymsDk2POitUCA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Thu, Aug 20, 2015 at 8:03 PM, Stephane Bailliez <sbailliez(at)gmail(dot)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.

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Stephane Bailliez 2015-08-21 12:07:26 Re: Most efficient way of querying M 'related' tables where N out of M may contain the key
Previous Message Stephane Bailliez 2015-08-21 00:03:32 Most efficient way of querying M 'related' tables where N out of M may contain the key