Otimizando a consulta usando a exibição na estrutura do EAV

Um aplicativo está gravando em um banco de dados que segue uma estrutura EAV, semelhante a esta:

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);

(Entendo que o EAV é um pouco controverso, mas esta questão não é sobre o EAV: este aplicativo herdado não pode ser alterado de qualquer maneira.)

Pode haver vários atributos, mas geralmente até 200 atributos por itens (geralmente semelhantes). Desses 200 atributos, há um grupo de cerca de 25 que são mais comuns que os demais e que são usados ​​com mais frequência nas consultas.

Para tornar mais fácil escrever novas consultas com base em alguns desses 25 atributos (os requisitos tendem a mudar e preciso ser flexível), escrevi uma visão que une a tabela de atributos para esses 25 atributos. Seguindo o exemplo acima, fica assim:

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';

Um relatório típico usaria apenas alguns desses 25 atributos, por exemplo:

SELECT id, description, height, width FROM exp_item;

Algumas dessas consultas não são tão rápidas quanto eu gostaria que fossem. Com EXPLAINo , notei que ainda eram feitos joins nas colunas não utilizadas, o que, em cerca de 25 joins quando apenas 3 ou 4 atributos são usados, está causando uma degradação desnecessária no desempenho.

É claro que executar todos os LEFT JOINs na exibição é normal, mas estou pensando se haveria uma maneira de manter essa exibição (ou algo semelhante: estou interessado principalmente em usar uma exibição para simplificar a maneira como me refiro aos atributos , mais ou menos como se fossem colunas) e evitar (automaticamente) o uso de joins nos atributos não utilizados para uma determinada consulta.

A única solução que encontrei até agora é definir uma visão específica para cada uma dessas consultas, que só se junta com base nos atributos que são usados. (Isso melhora a velocidade, como esperado, mas requer mais programação de visualizações todas as vezes, portanto, um pouco menos de flexibilidade.)

Existe uma maneira melhor de fazer isso? (Existe uma maneira melhor de "fingir" que a estrutura do EAV é uma única tabela bem estruturada, do ponto de vista de escrever as consultas, e não ter que fazer essas junções à esquerda desnecessárias?)

Estou usando o PostgreSQL 8.4. Existem cerca de 10.000 linhas iteme cerca de 500.000 linhas em item_attr. Eu não esperaria mais de 80 mil linhas iteme 4 milhões de linhas item_attr, o que acredito que um sistema moderno pode manipular sem muitos problemas. (Comentários sobre outros RDBMS/versões também são bem-vindos.)

EDIT : Apenas para expandir o uso de índices neste exemplo.

O PRIMARY KEY (item, name)cria implicitamente um índice em (item, name), conforme documentado na documentação CREATE TABLE . Considerando que ambos iteme namesão usados ​​com uma restrição de igualdade no JOIN, esse índice parece adequado de acordo com a documentação sobre índices multicolunas .

O exemplo a seguir mostra que esse índice parece ser usado, conforme esperado, sem nenhum índice adicional explícito:

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))