Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

An application is writing into a database that follows an EAV structure, similar to this:

CREATE TABLE item (
    id INTEGER PRIMARY KEY,
    description TEXT
);

CREATE TABLE item_attr (
    item INTEGER REFERENCES item(id),
    name TEXT,
    value INTEGER,
    PRIMARY KEY (item, name)
);

INSERT INTO item VALUES (1, 'Item 1');
INSERT INTO item_attr VALUES (1, 'height', 20);
INSERT INTO item_attr VALUES (1, 'width', 30);
INSERT INTO item_attr VALUES (1, 'weight', 40);
INSERT INTO item VALUES (2, 'Item 2');
INSERT INTO item_attr VALUES (2, 'height', 10);
INSERT INTO item_attr VALUES (2, 'weight', 35);

(I gather that EAV is a bit controversial, but this question isn't about EAV: this legacy application can't be changed anyway.)

There can be a number of attributes, but usually, up to 200 attributes per items (often similar). Out of these 200 attributes, there's a group of about 25 that are more common than the others and that are used more often in queries.

To make it easier to write new queries based on some of those 25 attributes (the requirements tend to change and I need to be flexible), I have written a view that joins the attribute table for these 25 attributes. Following the example above, this looks like this:

CREATE VIEW exp_item AS SELECT
   i.id AS id,
   i.description AS description,
   ia_height.value AS height,
   ia_width.value AS width,
   ia_weight.value AS weight,
   ia_depth.value AS depth
FROM item i
  LEFT JOIN item_attr ia_height ON i.id=ia_height.item AND ia_height.name='height'
  LEFT JOIN item_attr ia_width ON i.id=ia_width.item AND ia_width.name='width'
  LEFT JOIN item_attr ia_weight ON i.id=ia_weight.item AND ia_weight.name='weight'
  LEFT JOIN item_attr ia_depth ON i.id=ia_depth.item AND ia_depth.name='depth';

A typical report would only make use of a few of those 25 attributes, for example:

SELECT id, description, height, width FROM exp_item;

Some of these queries are not quite as fast as I wish they were. Using EXPLAIN, I have noticed that the joins on the unused columns were still made, which, on about 25 joins when only 3 or 4 attributes are used, is causing an unnecessary degradation in performance.

Of course, performing all the LEFT JOINs in the view is normal, but I'm wondering if there would be a way to keep this view (or something similar: I'm mainly interested in using a view to simplify the way I refer to attributes, more or less as if they were columns) and to avoid (automatically) to use joins on the unused attributes for a particular query.

The only workaround I've found so far is to define a specific view for each of these queries, that only joins based on the attributes that are used. (This does improve the speed, as expected, but requires more programming of views every time, thus a bit less flexibility.)

Is there a better way to do this? (It there a better way to "pretend" the EAV structure is a single well-structured table, from the point of view of writing the queries, and not to have to make these unnecessary left joins?)

I'm using PostgreSQL 8.4. There are about 10K rows in item and about 500K rows in item_attr. I wouldn't expect more than 80K rows in item and a 4M rows in item_attr, which I believe a modern system can handle without too much problem. (Comments regarding other RDBMS/versions are welcome too.)

EDIT: Just to expand on the usage of indices in this example.

The PRIMARY KEY (item, name) implicitly creates an index on (item, name), as documented in the CREATE TABLE documentation. Considering that both item and name are used with an equality constraint in the JOIN, this index seems suitable according to the documentation on multi-column indexes.

The following example shows this index seems to be used, as expected, without any explicit additional index:

EXPLAIN SELECT id, description, height, width FROM exp_item WHERE width < 100;

                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=28.50..203.28 rows=10 width=20)
   ->  Nested Loop Left Join  (cost=28.50..196.73 rows=10 width=16)
         ->  Nested Loop Left Join  (cost=28.50..190.18 rows=10 width=16)
               ->  Hash Join  (cost=28.50..183.64 rows=10 width=16)
                     Hash Cond: (ia_width.item = i.id)
                     ->  Seq Scan on item_attr ia_width  (cost=0.00..155.00 rows=10 width=8)
                           Filter: ((value < 100) AND (name = 'width'::text))
                     ->  Hash  (cost=16.00..16.00 rows=1000 width=12)
                           ->  Seq Scan on item i  (cost=0.00..16.00 rows=1000 width=12)
               ->  Index Scan using item_attr_pkey on item_attr ia_depth  (cost=0.00..0.64 rows=1 width=4)
                     Index Cond: ((i.id = ia_depth.item) AND (ia_depth.name = 'depth'::text))
         ->  Index Scan using item_attr_pkey on item_attr ia_weight  (cost=0.00..0.64 rows=1 width=4)
               Index Cond: ((i.id = ia_weight.item) AND (ia_weight.name = 'weight'::text))
   ->  Index Scan using item_attr_pkey on item_attr ia_height  (cost=0.00..0.64 rows=1 width=8)
         Index Cond: ((i.id = ia_height.item) AND (ia_height.name = 'height'::text))
share|improve this question

3 Answers 3

up vote 6 down vote accepted

This is one (of many) downsides of EAV designs.

You can't really improve the JOIN: because of the necessary complexity, a cost based optimiser won't get to the perfect plan. It finds "good enough"

Suggestions:

  • don't use a view: use aggregate type queries (eg COUNT(*) = 2 if I match both height and weight)
  • use a trigger to maintain a real (or sparse) table and query that

The first option scales better becauses a few indexes on the main EAV fact table can cover all queries nicely.

share|improve this answer

You don't mention an indexes on the eav table, so I'm assuming you don't have any.

It might make sense to add a few partial ones. Depending on the type of queries you're doing, either or both of these might be useful:

create index item_attr_weight_item_idx
  on item_attr(item)
  where (name = 'weight');

create index item_attr_weight_value_idx
  on item_attr(value)
  where (name = 'weight');

Alternatively, since you've a small number of rows, a big fat index on (name, value) or (name, item) might work. The latter can be made partial too, e.g.:

create index item_attr_freq_item_idx
  on item_attr(name, item)
  where (name in ('weight', 'height', 'width'));

That way, at least the query planner will have something more material to work with.

share|improve this answer
    
I'm not sure I understand whether an index tied to specific values of name would help here. There already is an index on (item, name) created with the PRIMARY KEY ("PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce uniqueness. Thus, it is not necessary to create an index explicitly for primary key columns.") –  Bruno Jun 27 '11 at 8:32
    
Yep, but for optimal planning, your query needs one with name to the left. Using your primary key index, Postgres would need to look up items one by one, and test for the name. What you want it to do, is to look up a single name, and to return every applicable item in one pass. –  Denis de Bernardy Jun 27 '11 at 9:06
    
@Denis, sorry, I'm not sure I understand this column-ordering subtlety, you're not really mentioning it in the link to your answer on do/don'ts. Considering that both item and name are used with an equality constraint on the join, how would an index on (name, item) help on top of the existing (item, name) one? The index on the value seems to help indeed, but for the initial problem, fetching unused attributes (and making unused joins through the view that's too general) that causes more overhead (a more specific view improves the performance more than having an index on the value). –  Bruno Jun 27 '11 at 18:43
    
If you've an index on (item, name) postgres will use the index if you've a constraint on item; the other way around, postgres will use them if you've a constraint on name. in your case, you want that to happen: only items with name equal to weight, rather than all ids filtered by name... –  Denis de Bernardy Jun 27 '11 at 20:12
    
Your example plan, for instance, could use an index on (value) where (name = 'width'). I'll bet the house that it'll prefer an index scan rather than a seq scan if you add it. If you add an index on (name, value) it'll likely do so as well. –  Denis de Bernardy Jun 27 '11 at 20:14

I would consider trying PostgreSQL's hstore module.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.