Bruno's questions

我正在使用 PostgreSQL (8.4) 来存储由频繁插入的应用程序生成的数据（在下面描述的表结构中）。

数据库随着时间不断增长，并且由于新数据比旧数据更相关（在此特定应用程序中），删除旧行是一个合理的解决方案（基于 lowerid或 old input_datetime，这或多或少相同） .

为了防止与此数据库（此服务器上运行的唯一数据库）相关的问题影响系统的其余部分，我已将 PostgreSQL 数据目录放在其自己的分区上（ext3，在 Linux 系统上）。然而，当这个分区变满时，这会导致许多问题。

我正在考虑定期删除旧数据（例如DELETE FROM data_group WHERE id <= ...通过 cron 作业）来处理这个问题。

首先，我对VACUUM（由启用的自动真空执行）的理解是，虽然它不一定将磁盘空间归还给操作系统（就像VACUUM FULL那样），但它仍然允许将一些新数据插入到已使用的磁盘空间（即DELETEs 不一定会影响文件大小，但它们仍会释放 PostgreSQL 自己的数据结构中的空间）。这个对吗？（我注意到VACUUM FULL应用程序本身存在一些问题，可能是因为它使用了锁。）

如果是这样，它似乎也SELECT pg_database_size('my_database')反映了磁盘上使用的大小，这不一定反映可用于进一步插入的内容。有没有另一种方法来估计有多少空间可用于新插入？

此外，当为时已晚，分区被填充到 100% 时，运行此DELETE语句会导致此错误并导致 PostgreSQL 服务崩溃：

恐慌：无法写入文件“pg_xlog/xlogtemp.7810”：设备上没有剩余空间

PostgreSQL 守护进程停止当然是一个主要问题（并且在这台机器上没有其他磁盘可以将集群移动到）。

是否有防止此类问题发生的一般策略（知道磁盘空间在给定分区内受到限制，但删除旧数据是可以接受的）？我想尽可能多地自动化这一切，而不需要root或postgres（或 PostgreSQL 管理员）干预。

CREATE TABLE data_group (
    id SERIAL PRIMARY KEY,
    name TEXT,
    input_datetime TIMESTAMPTZ
);

CREATE TABLE data_item (
    id SERIAL PRIMARY KEY,
    group_id INTEGER NOT NULL REFERENCES data_group(id) ON DELETE CASCADE ON UPDATE CASCADE,
    position INTEGER NOT NULL,
    data BYTEA
);

我正在使用 PostgreSQL 8.4.15。运行pg_dump备份数据库时，出现以下错误：

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  missing chunk number 0 for toast value 123456789 in pg_toast_987654321
pg_dump: The command was: COPY public.my_table (id, .... all the columns ...)

在搜索此错误消息时，我发现了一些建议重新索引表的参考资料（此处和此处）。（在这些讨论中，有提到查询pg_class表以找到正确的pg_toast_XXXXXX值，但似乎是因为它没有显示在他们的错误消息中。我跳过了这部分，因为我在错误消息中显示了一个值. 我想这可能是由于 PostgreSQL 的更高版本而带来的便利。）

我运行了以下内容：

REINDEX table pg_toast.pg_toast_987654321;
VACUUM ANALYZE my_table;

我现在可以毫无错误地使用pg_dump。

pg_toast这些命令实际上是做什么的？这些仅仅是简单的清理，还是他们已经摆脱了该表中的某些行？首先是什么导致了这个问题？

该表中大约有 300000 行，但我预计自上次成功备份以来只有大约 250 条新行（该表仅用于 INSERT/SELECT，没有 UPDATE）。

使用 PostgreSQL 8.4，我有许多具有非常相似结构但属于不同类别的表：

CREATE TABLE table_a (
    id SERIAL PRIMARY KEY,
    event_time TIMESTAMP WITH TIME ZONE NOT NULL,
    value_a REAL
);

CREATE TABLE table_b (
    id SERIAL PRIMARY KEY,
    event_time TIMESTAMP WITH TIME ZONE NOT NULL,
    value_b REAL
);

CREATE TABLE table_c (
    id SERIAL PRIMARY KEY,
    event_time TIMESTAMP WITH TIME ZONE NOT NULL,
    value_c REAL
);

我需要将这些值链接到中央表（根据查询使用连接或子选择）：

CREATE TABLE periods_table (
    id SERIAL PRIMARY KEY,
    start_time TIMESTAMP WITH TIME ZONE NOT NULL,
    end_time TIMESTAMP WITH TIME ZONE NOT NULL,
    category TEXT NOT NULL
);

这里,category是'Category A','Category B'或之一'Category C'。

为了抽象出 A、B 和 C 表之间的相似性，我创建了一个视图：

CREATE VIEW table_values AS
    SELECT 'Category A' AS category, event_time, value_a AS value
        FROM table_a
    UNION
    SELECT 'Category B' AS category, event_time, value_b AS value
        FROM table_b
    UNION
    SELECT 'Category C' AS category, event_time, value_c AS value
        FROM table_c;

典型的查询类似于：

SELECT p.start_time, p.end_time, p.category,
       (SELECT SUM(v.value) FROM table_values v
           WHERE v.category=p.category
             AND v.event_time >= t.start_time AND v.event_time < t.end_time)
    FROM periods_table p

问题在于category可能用于区分视图中不同表的列仅在最后使用。

即使是EXPLAIN ANALYZEonSELECT * FROM table_values WHERE category='Category A'显示当符合此条件的行将永远只来自时，所有 3 个表都被子查询table_a：

                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan table_values  (cost=176.02..295.50 rows=27 width=44) (actual time=0.135..0.135 rows=0 loops=1)
   Filter: (table_values.category = 'Category A'::text)
   ->  HashAggregate  (cost=176.02..229.12 rows=5310 width=12) (actual time=0.119..0.119 rows=0 loops=1)
         ->  Append  (cost=0.00..136.20 rows=5310 width=12) (actual time=0.089..0.089 rows=0 loops=1)
               ->  Subquery Scan "*SELECT* 1"  (cost=0.00..45.40 rows=1770 width=12) (actual time=0.025..0.025 rows=0 loops=1)
                     ->  Seq Scan on table_a  (cost=0.00..27.70 rows=1770 width=12) (actual time=0.010..0.010 rows=0 loops=1)
               ->  Subquery Scan "*SELECT* 2"  (cost=0.00..45.40 rows=1770 width=12) (actual time=0.020..0.020 rows=0 loops=1)
                     ->  Seq Scan on table_b  (cost=0.00..27.70 rows=1770 width=12) (actual time=0.006..0.006 rows=0 loops=1)
               ->  Subquery Scan "*SELECT* 3"  (cost=0.00..45.40 rows=1770 width=12) (actual time=0.020..0.020 rows=0 loops=1)
                     ->  Seq Scan on table_c  (cost=0.00..27.70 rows=1770 width=12) (actual time=0.006..0.006 rows=0 loops=1)
 Total runtime: 0.437 ms
(11 rows)

（这个虚拟表中没有数据，但实际表有大约 300000 行。SELECT * FROM table_values WHERE category='Category A'它比 花费的时间大约长 15 倍SELECT * FROM table_a，而它们或多或少是相同的。）

每个表上都有索引event_time，但由于视图上没有索引category，所以这无济于事。我还尝试用 CTE 替换视图（因为它有时会导致不同的查询路径），但没有帮助。

考虑到我无法真正更改现有表，有没有办法“合并”像这样的几个表，从而加快查询速度？

编辑：对实际数据的类似查询。（实际上这里有 5 个类似的表。）

有趣的是，虽然我在这里查询“类别 E”，但“类别 A”中有一个排序键并非来自查询中的任何特定位置（我猜它必须来自视图中的第一个选择，或者可能仅使用第一个选择中的值来指示列名）。

EXPLAIN ANALYZE SELECT * FROM table_values WHERE category='Category E':

Subquery Scan table_values  (cost=1573543.53..1755714.30 rows=40482 width=44) (actual time=221030.235..221234.162 rows=317676 loops=1)
  Filter: (table_values.category = 'Category E'::text)
  ->  Unique  (cost=1573543.53..1654508.32 rows=8096479 width=12) (actual time=212999.276..220240.297 rows=8097555 loops=1)
        ->  Sort  (cost=1573543.53..1593784.72 rows=8096479 width=12) (actual time=212999.275..218561.085 rows=8097555 loops=1)
              Sort Key: ('Category A'::text), "*SELECT* 1".event_time, "*SELECT* 1".value
              Sort Method:  external merge  Disk: 300792kB"
              ->  Append  (cost=0.00..229411.58 rows=8096479 width=12) (actual time=0.014..4683.734 rows=8097555 loops=1)
                    ->  Subquery Scan "*SELECT* 1"  (cost=0.00..80689.62 rows=2847831 width=12) (actual time=0.014..954.326 rows=2847951 loops=1)
                          ->  Seq Scan on table_a  (cost=0.00..52211.31 rows=2847831 width=12) (actual time=0.010..607.528 rows=2847951 loops=1)
                    ->  Subquery Scan "*SELECT* 2"  (cost=0.00..29304.52 rows=1033976 width=12) (actual time=9.738..576.803 rows=1034928 loops=1)
                          ->  Seq Scan on table_b  (cost=0.00..18964.76 rows=1033976 width=12) (actual time=9.737..450.619 rows=1034928 loops=1)
                    ->  Subquery Scan "*SELECT* 3"  (cost=0.00..30463.22 rows=1075161 width=12) (actual time=15.100..720.983 rows=1075157 loops=1)
                          ->  Seq Scan on table_c  (cost=0.00..19711.61 rows=1075161 width=12) (actual time=15.099..592.070 rows=1075157 loops=1)
                    ->  Subquery Scan "*SELECT* 4"  (cost=0.00..79952.70 rows=2821835 width=12) (actual time=20.098..1794.739 rows=2821843 loops=1)
                          ->  Seq Scan on table_d  (cost=0.00..51734.35 rows=2821835 width=12) (actual time=20.097..1441.719 rows=2821843 loops=1)
                    ->  Subquery Scan "*SELECT* 5"  (cost=0.00..9001.52 rows=317676 width=12) (actual time=0.016..108.768 rows=317676 loops=1)
                          ->  Seq Scan on table_e  (cost=0.00..5824.76 rows=317676 width=12) (actual time=0.016..69.732 rows=317676 loops=1)
Total runtime: 221299.573 ms

EXPLAIN ANALYZE SELECT * FROM table_e:

Seq Scan on table_e  (cost=0.00..5824.76 rows=317676 width=12) (actual time=0.025..54.143 rows=317676 loops=1)
Total runtime: 67.624 ms

有没有办法让非管理员 PostgreSQL 用户发现它可以在它所连接的服务器上访问哪些数据库，更具体地说，这些数据库中的哪些有一个具有特定名称的表，用户有足够的权限查看（并且可能询问）？

据我所知，MySQL 的等价物是这样的：

SELECT t.TABLE_SCHEMA FROM information_schema.`TABLES` AS t
    WHERE t.TABLE_NAME = 'the_table_name'

（这最好适用于 PostgreSQL 8.4，但如果已经更改，我也对 9.1 的解决方案感兴趣。）

我试图在 PostgreSQL (8.4) 中表示树结构，以便能够查询从根到给定节点的路径或找到子分支中的所有节点。

这是一个测试表：

CREATE TABLE tree_data_1 (
    forest_id TEXT NOT NULL,
    node_id TEXT NOT NULL,
    parent_id TEXT,
    node_type TEXT,
    description TEXT,
    PRIMARY KEY (forest_id, node_id),
    FOREIGN KEY (forest_id, parent_id) REFERENCES tree_data_1 (forest_id, node_id)
);
CREATE INDEX tree_data_1_forestid_parent_idx ON tree_data_1(forest_id, parent_id);
CREATE INDEX tree_data_1_forestid_idx ON tree_data_1(forest_id);
CREATE INDEX tree_data_1_nodeid_idx ON tree_data_1(node_id);
CREATE INDEX tree_data_1_parent_idx ON tree_data_1(parent_id);

(forest_id, node_id)每个节点都由（在另一个林中可以有另一个同名的节点）标识。每棵树都从一个根节点开始（其中parent_id为空），尽管我只期望每个森林有一个。

这是使用递归 CTE 的视图：

CREATE OR REPLACE VIEW tree_view_1 AS
    WITH RECURSIVE rec_sub_tree(forest_id, node_id, parent_id, depth, path, cycle) AS (
        SELECT td.forest_id, td.node_id, td.parent_id, 0, ARRAY[td.node_id], FALSE FROM tree_data_1 td
        UNION ALL
        SELECT td.forest_id, rec.node_id, td.parent_id, rec.depth+1, td.node_id || rec.path, td.node_id = ANY(rec.path)
            FROM tree_data_1 td, rec_sub_tree rec
            WHERE td.forest_id = rec.forest_id AND rec.parent_id = td.node_id AND NOT cycle
     )
     SELECT forest_id, node_id, parent_id, depth, path
         FROM rec_sub_tree;

这是文档中示例的略微修改版本，考虑到forest_id, 并且返回rec.node_id递归SELECT而不是返回td.node_id。

获取从根到给定节点的路径，可以使用此查询：

SELECT * FROM tree_view_1 WHERE forest_id='Forest A' AND node_id='...' AND parent_id IS NULL

得到一个子树，这个查询可以使用：

SELECT * FROM tree_view_1 WHERE forest_id='Forest A' AND parent_id='...'

在给定的森林中得到一棵完整的树：

SELECT * FROM tree_view_1 WHERE forest_id='Forest A' AND parent_id IS NULL

最后一个查询使用以下查询计划（可在explain.depesz.com上查看）：

 CTE Scan on rec_sub_tree  (cost=1465505.41..1472461.19 rows=8 width=132) (actual time=0.067..62480.876 rows=133495 loops=1)
   Filter: ((parent_id IS NULL) AND (forest_id = 'Forest A'::text))
   CTE rec_sub_tree
     ->  Recursive Union  (cost=0.00..1465505.41 rows=309146 width=150) (actual time=0.048..53736.585 rows=1645992 loops=1)
           ->  Seq Scan on tree_data_1 td  (cost=0.00..6006.16 rows=247316 width=82) (actual time=0.034..975.796 rows=247316 loops=1)
           ->  Hash Join  (cost=13097.90..145331.63 rows=6183 width=150) (actual time=2087.065..5842.870 rows=199811 loops=7)
                 Hash Cond: ((rec.forest_id = td.forest_id) AND (rec.parent_id = td.node_id))
                 ->  WorkTable Scan on rec_sub_tree rec  (cost=0.00..49463.20 rows=1236580 width=132) (actual time=0.017..915.814 rows=235142 loops=7)
                       Filter: (NOT cycle)
                 ->  Hash  (cost=6006.16..6006.16 rows=247316 width=82) (actual time=1871.964..1871.964 rows=247316 loops=7)
                       ->  Seq Scan on tree_data_1 td  (cost=0.00..6006.16 rows=247316 width=82) (actual time=0.017..872.725 rows=247316 loops=7)
 Total runtime: 62978.883 ms
(12 rows)

正如预期的那样，这不是很有效。我对它似乎没有使用任何索引感到部分惊讶。

考虑到这些数据会被经常读取但很少修改（可能每隔几周进行一次小修改），有哪些可能的技术来优化此类查询和/或数据表示？

编辑：我还想以深度优先的顺序检索树。使用ORDER BY path也会大大降低上述查询的速度。

用测试数据填充表的示例 Python 程序（需要Psycopg2），可能比我在更现实的情况下预期的要多一点：

from uuid import uuid4
import random
import psycopg2

random.seed(1234567890)
min_depth = 3
max_depth = 6
max_sub_width = 10
next_level_prob = 0.7

db_connection = psycopg2.connect(database='...')
cursor = db_connection.cursor()
query = "INSERT INTO tree_data_1(forest_id, node_id, parent_id) VALUES (%s, %s, %s)"

def generate_sub_tree(forest_id, parent_id=None, depth=0, node_ids=[]):
    if not node_ids:
        node_ids = [ str(uuid4()) for _ in range(random.randint(1, max_sub_width)) ]
    for node_id in node_ids:
        cursor.execute(query, [ forest_id, node_id, parent_id ])
        if depth < min_depth or (depth < max_depth and random.random() < next_level_prob):
            generate_sub_tree(forest_id, node_id, depth+1)

generate_sub_tree('Forest A', node_ids=['Node %d' % (i,) for i in range(10)])
generate_sub_tree('Forest B', node_ids=['Node %d' % (i,) for i in range(10)])

db_connection.commit()
db_connection.close()

使用 PostgreSQL (8.4)，我正在创建一个视图，该视图总结了几个表中的各种结果（例如，在视图中创建列a、、、），然后我需要在同一个查询中将其中一些结果组合在一起（例如，、、, ...)，从而产生最终结果。我注意到的是，每次使用中间结果时都会对其进行完全计算，即使它是在同一个查询中完成的。bca+ba-b(a+b)/c

有没有办法对此进行优化以避免每次都计算相同的结果？

这是一个重现问题的简化示例。

CREATE TABLE test1 (
    id SERIAL PRIMARY KEY,
    log_timestamp TIMESTAMP NOT NULL
);
CREATE TABLE test2 (
    test1_id INTEGER NOT NULL REFERENCES test1(id),
    category VARCHAR(10) NOT NULL,
    col1 INTEGER,
    col2 INTEGER
);
CREATE INDEX test_category_idx ON test2(category);

-- Added after edit to this question
CREATE INDEX test_id_idx ON test2(test1_id);

-- Populating with test data.
INSERT INTO test1(log_timestamp)
    SELECT * FROM generate_series('2011-01-01'::timestamp, '2012-01-01'::timestamp, '1 hour');
INSERT INTO test2
    SELECT id, substr(upper(md5(random()::TEXT)), 1, 1),
               (20000*random()-10000)::int, (3000*random()-200)::int FROM test1;
INSERT INTO test2
    SELECT id, substr(upper(md5(random()::TEXT)), 1, 1),
               (2000*random()-1000)::int, (3000*random()-200)::int FROM test1;
INSERT INTO test2
    SELECT id, substr(upper(md5(random()::TEXT)), 1, 1),
               (2000*random()-40)::int, (3000*random()-200)::int FROM test1;

这是执行最耗时操作的视图：

CREATE VIEW testview1 AS
    SELECT
       t1.id,
       t1.log_timestamp,
       (SELECT SUM(t2.col1) FROM test2 t2 WHERE t2.test1_id=t1.id AND category='A') AS a,
       (SELECT SUM(t2.col2) FROM test2 t2 WHERE t2.test1_id=t1.id AND category='B') AS b,
       (SELECT SUM(t2.col1 - t2.col2) FROM test2 t2 WHERE t2.test1_id=t1.id AND category='C') AS c
    FROM test1 t1;

• SELECT a FROM testview1产生这个计划（通过EXPLAIN ANALYZE）：

   Seq Scan on test1 t1  (cost=0.00..1787086.55 rows=8761 width=4) (actual time=12.877..10517.575 rows=8761 loops=1)
     SubPlan 1
       ->  Aggregate  (cost=203.96..203.97 rows=1 width=4) (actual time=1.193..1.193 rows=1 loops=8761)
             ->  Bitmap Heap Scan on test2 t2  (cost=36.49..203.95 rows=1 width=4) (actual time=1.109..1.177 rows=0 loops=8761)
                   Recheck Cond: ((category)::text = 'A'::text)
                   Filter: (test1_id = $0)
                   ->  Bitmap Index Scan on test_category_idx  (cost=0.00..36.49 rows=1631 width=0) (actual time=0.414..0.414 rows=1631 loops=8761)
                         Index Cond: ((category)::text = 'A'::text)
   Total runtime: 10522.346 ms
  

• SELECT a, a FROM testview1产生这个计划：

   Seq Scan on test1 t1  (cost=0.00..3574037.50 rows=8761 width=4) (actual time=3.343..20550.817 rows=8761 loops=1)
     SubPlan 1
       ->  Aggregate  (cost=203.96..203.97 rows=1 width=4) (actual time=1.183..1.183 rows=1 loops=8761)
             ->  Bitmap Heap Scan on test2 t2  (cost=36.49..203.95 rows=1 width=4) (actual time=1.100..1.166 rows=0 loops=8761)
                   Recheck Cond: ((category)::text = 'A'::text)
                   Filter: (test1_id = $0)
                   ->  Bitmap Index Scan on test_category_idx  (cost=0.00..36.49 rows=1631 width=0) (actual time=0.418..0.418 rows=1631 loops=8761)
                         Index Cond: ((category)::text = 'A'::text)
     SubPlan 2
       ->  Aggregate  (cost=203.96..203.97 rows=1 width=4) (actual time=1.154..1.154 rows=1 loops=8761)
             ->  Bitmap Heap Scan on test2 t2  (cost=36.49..203.95 rows=1 width=4) (actual time=1.083..1.143 rows=0 loops=8761)
                   Recheck Cond: ((category)::text = 'A'::text)
                   Filter: (test1_id = $0)
                   ->  Bitmap Index Scan on test_category_idx  (cost=0.00..36.49 rows=1631 width=0) (actual time=0.426..0.426 rows=1631 loops=8761)
                         Index Cond: ((category)::text = 'A'::text)
   Total runtime: 20557.581 ms
  

在这里，选择a, a花费的时间是选择的两倍a，而它们实际上可以只计算一次。例如，使用SELECT a, a+b, a-b FROM testview1，它通过子计划a3 次和b两次，而执行时间可以减少到总时间的 2/5（假设 + 和 - 在这里可以忽略不计）。

这是一件好事，它不会在不需要时计算未使用的列 (b和c)，但是有没有办法让它只从视图中计算相同的使用列一次？

编辑： @Frank Heikens 正确地建议使用上面示例中缺少的索引。虽然它确实提高了每个子计划的速度，但它不会阻止多次计算相同的子查询。抱歉，我应该在最初的问题中说明这一点。

在 PostgreSQL (8.4) 中，我试图将字符串参数转换为 SQL 查询中的日期，now()当字符串不是有效日期（或为空）时回退。

在“伪 SQL”中，这将是这样的：

SELECT CASE WHEN ? is not a valid date THEN now()::DATE ELSE CAST(? AS DATE) END;

我尝试使用这两个查询来简化问题以检测空字符串：

SELECT CASE WHEN ?='' THEN now()::DATE ELSE CAST(? AS DATE) END;
SELECT DATE(CASE WHEN ?='' THEN now() ELSE ?  END);

例如，如果参数是''，则等效于：

SELECT CASE WHEN ''='' THEN now()::DATE ELSE CAST('' AS DATE) END;
SELECT DATE(CASE WHEN ''='' THEN now() ELSE ''  END);

两者都失败了ERROR: invalid input syntax for type timestamp with time zone: "" It 是有道理的，但这意味着无论条件是否为真ELSE，都会评估块（或至少解析其类型） 。CASE以下有效，但我希望CASE（或类似）条件处理的情况恰恰是它不是有效日期的情况。

SELECT CASE WHEN '2011-12-01'='' THEN now()::DATE ELSE CAST('2011-12-01' AS DATE) END;

我最接近工作解决方案的是：

SELECT DATE(COALESCE(NULLIF(?, '')::timestamptz, now()));

在这种情况下，如果参数为''，则返回当前日期，否则返回字符串参数中传递的日期（前提是可以转换为有效日期）。

我想要更进一步，让任何不能变成DATE使用的东西成为当前日期。我想这可以使用会捕获此错误的自定义 PL/pgSQL 函数来完成，但是如果没有这样的函数，可以在“普通”SQL（或至少使用现有的 PostgreSQL 函数）中完成吗？

应用程序正在写入遵循 EAV 结构的数据库，类似于：

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

（我认为 EAV 有点争议，但这个问题与 EAV 无关：这个遗留应用程序无论如何都无法更改。）

可以有多个属性，但通常每个项目最多 200 个属性（通常相似）。在这 200 个属性中，大约有 25 个属性比其他属性更常见，并且在查询中使用得更频繁。

为了更容易根据这 25 个属性中的一些属性编写新查询（需求往往会发生变化，我需要灵活一些），我编写了一个视图来连接这 25 个属性的属性表。按照上面的示例，它看起来像这样：

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

一份典型的报告只会使用这 25 个属性中的几个，例如：

SELECT id, description, height, width FROM exp_item;

其中一些查询没有我希望的那么快。使用EXPLAIN时，我注意到未使用列上的连接仍然存在，当仅使用 3 或 4 个属性时，在大约 25 个连接上，这会导致性能不必要的下降。

当然，LEFT JOIN在视图中执行所有 s 是正常的，但我想知道是否有办法保留这个视图（或类似的东西：我主要感兴趣的是使用视图来简化我引用属性的方式，或多或少好像它们是列）并避免（自动）对特定查询的未使用属性使用连接。

到目前为止，我发现的唯一解决方法是为每个查询定义一个特定视图，该视图仅基于所使用的属性进行连接。（这确实提高了速度，正如预期的那样，但每次都需要更多的视图编程，因此灵活性有点低。）

有一个更好的方法吗？（从编写查询的角度来看，是否有更好的方法可以“假装” EAV 结构是一个结构良好的表，而不必进行这些不必要的左连接？）

我正在使用 PostgreSQL 8.4。中有大约 10K 行item和大约 500K 行item_attr。我不期望超过 80K 行item和 4M 行item_attr，我相信现代系统可以毫无问题地处理。（也欢迎对其他 RDBMS/版本发表评论。）

编辑：只是为了扩展这个例子中索引的使用。

PRIMARY KEY (item, name)隐式创建索引(item, name)，如CREATE TABLE文档中所述。考虑到item和name都与 中的等式约束一起使用JOIN，根据关于多列索引的文档，该索引似乎很合适。

下面的示例显示该索引似乎按预期使用，没有任何明确的附加索引：

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