Postgresql : Alternative to joining the same table multiple times

Question

If i have two tables entry and entry_metadata, with the entry_metadata as a description table for the entry referenced by entry_id and a variable.

If i have this :

entry

id | name   |
-------------
1  | entry1 |
2  | entry2 |
3  | entry3 |

entry_metadata

id | entry_id | variable | value
1  |    1     | width    | 10
2  |    1     | height   | 5
3  |    2     | width    | 8
4  |    2     | height   | 7
5  |   ...    |  ....    | ..

and i'm getting the table :

id | name   | width | height| ...  | ...
-----------------------------------------
1  | entry1 |  10   |  5    |
2  | entry2 |  8    |  7    |
3  | entry3 |  ..   |  ..   |

by the sql :

select e.name, em.width, emr.height
from
  public.entry e
left join
  public.entry_metadata em
on
  em.entry_id = e.id and em.variable = 'width'
left join
  public.entry_metadata emr
on
  emr.entry_id = e.id and emr.variable = 'height'

The query above works. But as I add more variables to get the values (the entry_metadata table includes a large variety of variables) from the entry metadata. The query gets really really slow. every join I do slows down the execution greatly. Is there a way to get around this?

score 2 · Answer 1 · answered May 28 '15 at 02:42

2

You can also do this with conditional aggregation:

select id, name,
       max(case when variable = 'width' then value end) as width,
       max(case when variable = 'height' then value end) as height
from public.entry_metadata em
group by id, name;

Adding additional columns is just adding more aggregation functions.

answered May 28 '15 at 02:42

Gordon Linoff

1,242,037
58
646
786

would this be faster than the join alternative? – muffin May 28 '15 at 05:17
@muffin . . . You need to try it on your data and your system. WIth lots of columns, it is likely to be faster, but there are no guarantees. – Gordon Linoff May 28 '15 at 11:59

sfrutig · Answer 2 · 2015-05-28T06:22:08.737

1

Just use subselects for this:

SELECT
  e.id,
  e.name,
  (SELECT em.value FROM public.entry_metadata em WHERE em.entry_id = e.id AND em.variable = 'width') AS width,
  (SELECT em.value FROM public.entry_metadata em WHERE em.entry_id = e.id AND em.variable = 'height') AS height
FROM
  public.entry e

So for each new variable you just need to add one more subselect.

edited May 28 '15 at 06:22

answered May 28 '15 at 02:54

sfrutig

277
2
14

i've tested it but it's a lot slower than the join – muffin May 28 '15 at 05:38
Do you have indexed your tables? Maybe missing index on entry_metadata.entry_id? – sfrutig May 28 '15 at 05:41
1

yeah.. it's actually good if it's 2 joins.. but if i'm getting 3 to 4.. the performance toll is exponential.. – muffin May 28 '15 at 05:43

score -1 · Answer 3 · answered May 28 '15 at 15:44

Is there a way to get around this?

Yes, replace entry_metadata table with addtional column in entry (possible solutions are hstore or jsonb) with key - value storage of entry metadata.

Btw. your tables represents well known controversial database desing pattern known as "Entity Attribute Value".

Postgresql : Alternative to joining the same table multiple times

3 Answers3