PostgreSQL：如何构建和索引与时间相关的数据以获得最佳查询性能？

概述问题： 我在我的数据库中有时间相关的数据,我正在努力组织,结构和索引这些数据,以便用户有效地检索数据;即使是简单的数据库查询也需要更长时间才能接受. 项目背景： 虽然这是一个纯数据库问题,但一些上下文可能有助于了解数据模型： 该项目围绕着一个庞大而复杂的机器进行研究.我不太了解机器本身,但实验室的谣言有一个flux capacitor在那里 – 我认为昨天,我发现Schrödinger’s cat 问题：

我在我的数据库中有时间相关的数据,我正在努力组织,结构和索引这些数据,以便用户有效地检索数据;即使是简单的数据库查询也需要更长时间才能接受.

项目背景：

虽然这是一个纯数据库问题,但一些上下文可能有助于了解数据模型：

该项目围绕着一个庞大而复杂的机器进行研究.我不太了解机器本身,但实验室的谣言有一个flux capacitor在那里 – 我认为昨天,我发现Schrödinger’s cat的尾巴挂在一边;-)

我们测量许多不同的参数,而机器运行时,使用位于整个机器上的传感器在不同的测量点(所谓的点)以一定时间间隔进行运行.我们不仅使用一个设备来测量这些参数,而且使用它们的全部范围;他们的测量数据的质量有所不同(我认为这涉及到采样率,传感器质量,价格以及许多其他不关心的方面);该项目的一个目标实际上是建立这些设备之间的比较.您可以将这些测量设备可视化为一堆实验手推车,每个实验手推车都有大量电缆连接到机器,每个都提供测量数据.

数据模型：

每个参数都有每个位置和每个设备的测量数据,例如在6天内每分钟一次.我的工作是将数据存储在数据库中,并提供有效的访问.

简而言之：

一个设备有一个唯一的名字
>一个参数也有一个名字;它们不是唯一的,所以它也有一个ID
>一个地点有一个ID

当然,项目数据库更复杂,但这些细节与这个问题似乎并不相关.

>测量数据索引具有ID,测量完成时的时间戳,对设备的引用和进行测量的点
>测量数据值具有对参数的引用和实际测量的值

最初,我已经将测量数据值建模为具有自己的ID作为主键;测量数据索引和值之间的n：m关系是一个单独的表,只存储索引：值ID对,但是由于该表本身消耗了相当多的硬盘空间,所以我们将其删除并将值ID更改为一个简单的整数存储其所属的测量数据索引的ID;测量数据值的主键现在由该ID和参数ID组成.

在旁注：当我创建数据模型时,我仔细遵循常用的设计指南,如3NF和适当的表约束(如唯一键);另一个经验法则是为每个外键创建一个索引.我怀疑,“严格”3NF的测量数据索引/值表的偏差可能是我正在查看的性能问题的原因之一,但是将数据模型更改回来并没有解决问题.

DDL中的数据模型：

注意：以下进一步更新此代码.

下面的脚本创建数据库和所有表.请注意,还没有明确的索引.在运行这个之前,请确保您没有碰巧已经有一个名为so_test的数据库与任何有价值的数据…

\c postgresDROP DATABASE IF EXISTS so_test;CREATE DATABASE so_test;\c so_testCREATE table device(  name VARCHAR(16) NOT NulL,CONSTRAINT device_pk PRIMARY KEY (name));CREATE table parameter(  -- must have ID as names are not unique  ID SERIAL,name VARCHAR(64) NOT NulL,CONSTRAINT parameter_pk PRIMARY KEY (ID));CREATE table spot(  ID SERIAL,CONSTRAINT spot_pk PRIMARY KEY (ID));CREATE table measurement_data_index(  ID SERIAL,fk_device_name VARCHAR(16) NOT NulL,fk_spot_ID INTEGER NOT NulL,t_stamp TIMESTAMP NOT NulL,CONSTRAINT measurement_pk PRIMARY KEY (ID),CONSTRAINT measurement_data_index_fk_2_device FOREIGN KEY (fk_device_name)    REFERENCES device (name) MATCH FulL    ON UPDATE NO ACTION ON DELETE NO ACTION,CONSTRAINT measurement_data_index_fk_2_spot FOREIGN KEY (fk_spot_ID)    REFERENCES spot (ID) MATCH FulL    ON UPDATE NO ACTION ON DELETE NO ACTION,CONSTRAINT measurement_data_index_uk_all_cols UNIQUE (fk_device_name,fk_spot_ID,t_stamp));CREATE table measurement_data_value(  ID INTEGER NOT NulL,fk_parameter_ID INTEGER NOT NulL,value VARCHAR(16) NOT NulL,CONSTRAINT measurement_data_value_pk PRIMARY KEY (ID,fk_parameter_ID),CONSTRAINT measurement_data_value_fk_2_parameter FOREIGN KEY (fk_parameter_ID)    REFERENCES parameter (ID) MATCH FulL    ON UPDATE NO ACTION ON DELETE NO ACTION);

我还创建了一个脚本来填写一些测试数据：

CREATE OR REPLACE FUNCTION insert_data()RETURNS VOIDLANGUAGE plpgsqlAS$BODY$  DECLARE    t_stamp  TIMESTAMP := '2012-01-01 00:00:00';    index_ID INTEGER;    param_ID INTEGER;    dev_name VARCHAR(16);    value    VARCHAR(16);  BEGIN    FOR dev IN 1..5    LOOP      INSERT INTO device (name) VALUES ('dev_' || to_char(dev,'FM00'));    END LOOP;    FOR param IN 1..20    LOOP      INSERT INTO parameter (name) VALUES ('param_' || to_char(param,'FM00'));    END LOOP;    FOR spot IN 1..10    LOOP      INSERT INTO spot (ID) VALUES (spot);    END LOOP;    WHILE t_stamp < '2012-01-07 00:00:00'    LOOP      FOR dev IN 1..5      LOOP        dev_name := 'dev_' || to_char(dev,'FM00');        FOR spot IN 1..10        LOOP          INSERT INTO measurement_data_index            (fk_device_name,t_stamp)            VALUES (dev_name,spot,t_stamp) RETURNING ID INTO index_ID;          FOR param IN 1..20          LOOP            SELECT ID INTO param_ID FROM parameter              WHERE name = 'param_' || to_char(param,'FM00');            value := 'd'  || to_char(dev,'FM00')                  || '_s' || to_char(spot,'FM00')                  || '_p' || to_char(param,'FM00');            INSERT INTO measurement_data_value (ID,fk_parameter_ID,value)              VALUES (index_ID,param_ID,value);          END LOOP;        END LOOP;      END LOOP;      t_stamp := t_stamp + '1 minute'::INTERVAL;    END LOOP;  END;$BODY$;SELECT insert_data();

PostgreSQL查询计划器需要最新统计信息,因此要分析所有表.可能不需要抽真空,但无论如何：

VACUUM ANALYZE device;VACUUM ANALYZE measurement_data_index;VACUUM ANALYZE measurement_data_value;VACUUM ANALYZE parameter;VACUUM ANALYZE spot;

示例查询：

如果我现在运行一个非常简单的查询,例如获取某个参数的所有值,它已经需要几秒钟,尽管数据库还不是很大：

EXPLAIN (ANALYZE ON,BUFFERS ON)SELECT measurement_data_value.value  FROM measurement_data_value,parameter WHERE measurement_data_value.fk_parameter_ID = parameter.ID   AND parameter.name = 'param_01';

我的开发机器的示例结果(有关我的环境的一些细节,请参见下文)：

query PLAN                                                                ------------------------------------------------------------------------------------------------------------------------------------------ Hash Join  (cost=1.26..178153.26 rows=432000 wIDth=12) (actual time=0.046..2281.281 rows=432000 loops=1)   Hash Cond: (measurement_data_value.fk_parameter_ID = parameter.ID)   Buffers: shared hit=55035   ->  Seq Scan on measurement_data_value  (cost=0.00..141432.00 rows=8640000 wIDth=16) (actual time=0.004..963.999 rows=8640000 loops=1)         Buffers: shared hit=55032   ->  Hash  (cost=1.25..1.25 rows=1 wIDth=4) (actual time=0.010..0.010 rows=1 loops=1)         Buckets: 1024  Batches: 1  Memory Usage: 1kB         Buffers: shared hit=1         ->  Seq Scan on parameter  (cost=0.00..1.25 rows=1 wIDth=4) (actual time=0.004..0.008 rows=1 loops=1)               Filter: ((name)::text = 'param_01'::text)               Buffers: shared hit=1 Total runtime: 2313.615 ms(12 rows)

数据库中没有隐含的索引,所以计划者只执行顺序扫描并不奇怪.如果我遵循似乎是经验法则,并为每个外键添加btree索引

CREATE INDEX measurement_data_index_IDx_fk_device_name    ON measurement_data_index (fk_device_name);CREATE INDEX measurement_data_index_IDx_fk_spot_ID    ON measurement_data_index (fk_spot_ID);CREATE INDEX measurement_data_value_IDx_fk_parameter_ID    ON measurement_data_value (fk_parameter_ID);

然后进行另一个真空分析(只是为了安全)并重新运行查询,计划程序使用位图堆和位图索引扫描,总查询时间有所改善：

query PLAN                                                                                   -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- nested Loop  (cost=8089.19..72842.42 rows=431999 wIDth=12) (actual time=66.773..1336.517 rows=432000 loops=1)   Buffers: shared hit=55033 read=1184   ->  Seq Scan on parameter  (cost=0.00..1.25 rows=1 wIDth=4) (actual time=0.005..0.012 rows=1 loops=1)         Filter: ((name)::text = 'param_01'::text)         Buffers: shared hit=1   ->  Bitmap Heap Scan on measurement_data_value  (cost=8089.19..67441.18 rows=431999 wIDth=16) (actual time=66.762..1237.488 rows=432000 loops=1)         Recheck Cond: (fk_parameter_ID = parameter.ID)         Buffers: shared hit=55032 read=1184         ->  Bitmap Index Scan on measurement_data_value_IDx_fk_parameter_ID  (cost=0.00..7981.19 rows=431999 wIDth=0) (actual time=65.222..65.222 rows=432000 loops=1)               Index Cond: (fk_parameter_ID = parameter.ID)               Buffers: shared read=1184 Total runtime: 1371.716 ms(12 rows)

然而,这仍然是一个非常简单的查询的执行时间的二分之一.

我到目前为止做了什么

得到自己的PostgreSQL 9.0 High Performance的副本 – 好书！
>做了一些基本的Postgresql服务器配置,看下面的环境
>创建了一个框架,使用项目中的实际查询运行一系列性能测试,并以图形方式显示结果;这些查询使用设备,点,参数和时间间隔作为输入参数,并且测试系列遍历例如. 5,10个设备,5,10个点,10,15,20参数和1..7天.基本的结果是它们太慢了,但是他们的查询计划对我来说太复杂了,所以我回到上面使用的真正简单的查询.

我已经研究了partitioning的价值表.数据是时间相关的,分区似乎是组织这种数据的适当手段;即使examples在Postgresql文档中也使用类似的东西.不过,我在same article阅读：

The benefits will normally be worthwhile only when a table would otherwise be very large. The exact point at which a table will benefit from partitioning depends on the application,although a rule of thumb is that the size of the table should exceed the physical memory of the database server.

整个测试数据库的大小小于1GB,我在具有8GB RAM的开发机器上运行我的测试,并在具有1GB的虚拟机上运行我的测试(另见下面的环境),因此表格远不是很大,甚至超过物理记忆.我可能会在某个阶段实施分区,但我有一种感觉,方法并不针对性能问题本身.

此外,我正在考虑cluster的价值表.我不喜欢在插入新数据时必须重新进行聚类,并且还需要排它的读/写锁定,但是查看this的SO问题,似乎无论如何,它有其优点并且可能是一个选项.然而,聚类是在一个索引上进行的,因为最多可以有4个选择标准进入查询(设备,参数和时间),所以我必须为所有这些创建集群,这反过来给我的印象是我根本没有创建正确的索引

我的环境：

> MacBook Pro(2009年年中)正在进行开发,具有双核cpu和8GB RAM
>我正在一台虚拟的Debian 6.0机器上运行数据库性能测试,该机器具有1 MB的内存,托管在MBP上
> Postgresql版本是9.1,因为这是最新版本,当我安装它,升级到9.2将是可能的
>我已经将shared_buffers从默认的1600kB更改为2500的RAM,这两台机器都是按PostgreSQL docs所推荐的(其中包括扩大kernel settings像SHMALL,SHMMAX等)
>类似地,我已将effective_cache_size从默认的128MB更改为可用RAM的50％
>我使用不同的work_mem设置进行性能测试,但是在性能上没有看到任何重大差异

注意：我认为重要的一个方面是,具有来自项目的真实查询的性能测试系列在具有8GB的MacBook和具有1GB的虚拟机之间的性能上并不相同;即如果在MacBook上查询需要10秒,则VM上也需要10秒钟.此外,我在更改shared_buffers,effective_cache_size和work_mem之前和之后执行相同的性能测试,配置更改没有将性能提高10％以上;一些结果实际上甚至变得更糟,所以看起来任何差异都是由测试变化而不是配置变化引起的.这些观察结果使我相信RAM和postgres.conf设置不是这里的限制因素.

我的问题：

我不知道不同的或附加的索引是否会加快查询速度,如果是这样,那么要创建哪个.查看数据库的大小以及查询的简单程度,我的印象是我的数据模型有根本的错误,或者我如何选择我的索引到目前为止.

有没有人有一些建议我如何结构和索引时间相关我的提高查询性能？

被问到更广泛的是调优查询性能

>通常在事件基础上完成“,即一次查询不能令人满意地执行？看来我的所有查询都太慢了
>主要是查询(和理解)查询计划的问题,然后添加索引并衡量事情是否改善,可能通过应用自己的经验加速过程？

如何让这个数据库飞行？

更新01：

看看到目前为止的回应,我想我还没有正确地解释测量数据索引/值表的需要,所以让我再试一次.存储空间是这里的问题.

注意：

>这里使用的数字更多是说明性目的,仅用于比较,即数字本身不相关,重要的是使用单个表与使用索引和值表之间的存储需求的百分比差异
> Postgresql数据类型存储大小记录在this章节
这并不意味着科学上是正确的,例如单位大概是数学假的;数字应该加起来

假设

> 1天的测量
>每分钟1组测量
> 10个设备
> 10个参数
> 10点

这加起来

1 meas/min x 60 min/hour x 24 hour/day = 1440 meas/day

每个测量每个参数都有来自每个位置和每个设备的数据,所以

10 spots x 10 devices x 10 parameters = 1000 data sets/meas

所以总共

1440 meas/day x 1000 data sets/meas = 1 440 000 data sets/day

如果我们将单个表中的所有测量值存储为Catcall suggested,例如

CREATE table measurement_data(  device_name character varying(16) NOT NulL,spot_ID integer NOT NulL,parameter_ID integer NOT NulL,t_stamp timestamp without time zone NOT NulL,value character varying(16) NOT NulL,-- constraints...);

单行将加起来

17 + 4 + 4 + 8 + 17 = 50 bytes/row

在最坏的情况下,所有的varchar字段都被完全填满.这相当于

50 bytes/row x 1 440 000 rows/day = 72 000 000 bytes/day

或每天约69 MB.

虽然这听起来不是很多,但实际数据库中的存储空间需求将是非常高的(再次,这里使用的数字仅仅是为了说明).因此,我们将测量数据分解成索引和值表,如前面在问题中所述：

CREATE table measurement_data_index(  ID SERIAL,-- constraints...);CREATE table measurement_data_value(  ID INTEGER NOT NulL,-- constraints...);

值行的ID等于其所属索引的ID.

索引和值表中的一行的大小

index: 4 + 17 + 4 + 8 = 33 bytesvalue: 4 + 4 + 17     = 25 bytes

(再次,最坏的情况).总行数是

index: 10 devices x 10 spots x 1440 meas/day =   144 000 rows/dayvalue: 10 parameters x 144 000 rows/day      = 1 440 000 rows/day

所以总是

index: 33 bytes/row x   144 000 rows/day =  4 752 000 bytes/dayvalue: 25 bytes/row x 1 440 000 rows/day = 36 000 000 bytes/daytotal:                                   = 40 752 000 bytes/day

或每天约39 MB,而不是单一表解决方案的〜69 MB.

更新02(re：wildplassers response)：

这个问题已经很长了,所以我正在考虑在上面的原始问题中更新代码,但我认为这可能有助于在这里获得第一个和改进的解决方案,以更好地看到差异.

与原始方法相比有变化(有些按重要性排列)：

>交换时间戳和参数,即将t_stamp字段从measurement_data_index表移动到measurement_data_value,并将fk_parameter_ID字段从值移动到索引表：通过此更改,索引表中的所有字段都是常量,并且新的测量数据仅写入值表.我没有想到任何主要的查询性能改进(我错了),但我觉得它使测量数据索引概念更清晰.虽然它需要更多的存储空间(根据一些相当粗略的估计),但是当根据读/写需求将tablespaces移动到不同的硬盘驱动器时,拥有“静态”索引表也可能有助于部署.
>在设备表中使用代理键：从我的理解中,代理键是从数据库设计的角度来看不是严格要求的主键(例如,设备名称已经是唯一的,因此也可以用作PK)但可能有助于提高查询性能.我添加了它,因为再次,如果索引表仅引用ID(而不是一些名称和一些ID),我觉得它使得概念更清晰.
>重写insert_data()：使用generate_serIEs()而不是嵌套的FOR循环;使代码变得非常’snappIEr’.
>作为这些更改的副作用,插入测试数据只需要第一个解决方案所需时间的大约50％.
>我没有添加视野,因为wildplaser建议;没有向后兼容性.
>索引表中FK的附加索引似乎被查询计划程序忽略,对查询计划或性能没有影响.

(似乎没有这一行,下面的代码没有正确显示为SO页面上的代码…)

\c postgresDROP DATABASE IF EXISTS so_test_03;CREATE DATABASE so_test_03;\c so_test_03CREATE table device(  ID SERIAL,name VARCHAR(16) NOT NulL,CONSTRAINT device_pk PRIMARY KEY (ID),CONSTRAINT device_uk_name UNIQUE (name));CREATE table parameter(  ID SERIAL,fk_device_ID    INTEGER NOT NulL,fk_spot_ID      INTEGER NOT NulL,CONSTRAINT measurement_data_index_fk_2_device FOREIGN KEY (fk_device_ID)    REFERENCES device (ID) MATCH FulL    ON UPDATE NO ACTION ON DELETE NO ACTION,CONSTRAINT measurement_data_index_fk_2_parameter FOREIGN KEY (fk_parameter_ID)    REFERENCES parameter (ID) MATCH FulL    ON UPDATE NO ACTION ON DELETE NO ACTION,CONSTRAINT measurement_data_index_uk_all_cols UNIQUE (fk_device_ID,fk_spot_ID));CREATE table measurement_data_value(  ID INTEGER NOT NulL,-- NOTE: inverse fIEld order compared to wildplassers version  CONSTRAINT measurement_data_value_pk PRIMARY KEY (ID,t_stamp),CONSTRAINT measurement_data_value_fk_2_index FOREIGN KEY (ID)    REFERENCES measurement_data_index (ID) MATCH FulL    ON UPDATE NO ACTION ON DELETE NO ACTION);CREATE OR REPLACE FUNCTION insert_data()RETURNS VOIDLANGUAGE plpgsqlAS$BODY$  BEGIN    INSERT INTO device (name)    SELECT 'dev_' || to_char(item,'FM00')    FROM generate_serIEs(1,5) item;    INSERT INTO parameter (name)    SELECT 'param_' || to_char(item,20) item;    INSERT INTO spot (name)    SELECT 'spot_' || to_char(item,10) item;    INSERT INTO measurement_data_index (fk_device_ID,fk_spot_ID)    SELECT device.ID,parameter.ID,spot.ID    FROM device,parameter,spot;    INSERT INTO measurement_data_value(ID,t_stamp,value)    SELECT index.ID,item,'d'  || to_char(index.fk_device_ID,'FM00') ||           '_s' || to_char(index.fk_spot_ID,'FM00') ||           '_p' || to_char(index.fk_parameter_ID,'FM00')    FROM measurement_data_index index,generate_serIEs('2012-01-01 00:00:00','2012-01-06 23:59:59',interval '1 min') item;  END;$BODY$;SELECT insert_data();

在某些阶段,我将更改我自己的约定,使用内联PRIMARY KEY和REFERENCES语句,而不是明确的CONSTRAINT;目前来看,我认为保持这样的方式可以更容易地比较两种解决方案.

不要忘记更新查询计划器的统计信息：

VACUUM ANALYZE device;VACUUM ANALYZE measurement_data_index;VACUUM ANALYZE measurement_data_value;VACUUM ANALYZE parameter;VACUUM ANALYZE spot;

运行一个应该产生与第一种方法相同的结果的查询：

EXPLAIN (ANALYZE ON,BUFFERS ON)SELECT measurement_data_value.value  FROM measurement_data_index,measurement_data_value,parameter WHERE measurement_data_index.fk_parameter_ID = parameter.ID   AND measurement_data_index.ID = measurement_data_value.ID   AND parameter.name = 'param_01';

结果：

nested Loop  (cost=0.00..34218.28 rows=431998 wIDth=12) (actual time=0.026..696.349 rows=432000 loops=1)  Buffers: shared hit=435332  ->  nested Loop  (cost=0.00..29.75 rows=50 wIDth=4) (actual time=0.012..0.453 rows=50 loops=1)        Join Filter: (measurement_data_index.fk_parameter_ID = parameter.ID)        Buffers: shared hit=7        ->  Seq Scan on parameter  (cost=0.00..1.25 rows=1 wIDth=4) (actual time=0.005..0.010 rows=1 loops=1)              Filter: ((name)::text = 'param_01'::text)              Buffers: shared hit=1        ->  Seq Scan on measurement_data_index  (cost=0.00..16.00 rows=1000 wIDth=8) (actual time=0.003..0.187 rows=1000 loops=1)              Buffers: shared hit=6  ->  Index Scan using measurement_data_value_pk on measurement_data_value  (cost=0.00..575.77 rows=8640 wIDth=16) (actual time=0.013..12.157 rows=8640 loops=50)        Index Cond: (ID = measurement_data_index.ID)        Buffers: shared hit=435325Total runtime: 726.125 ms

这几乎是第一种方法所需的1.3倍的一半;考虑到我正在加载432K行,这是我现在可以生活的结果.

注意：值表PK中的字段顺序为ID,t_stamp;在wildplassers响应中的顺序是t_stamp,whw_ID.我这样做是因为我感觉到一个“常规”字段顺序是在表声明中列出的字段顺序(而“反向”则是相反的方式),但这只是我自己的约定,使我不会得到困惑.无论哪种方式,正如Erwin Brandstetter所指出的,这个顺序对于性能的改善是绝对关键的;如果是错误的方式(而且在野生动物解决方案中缺少一个反向索引),则查询计划如下所示,并且性能比以下三倍更糟：

Hash Join  (cost=22.14..186671.54 rows=431998 wIDth=12) (actual time=0.460..2570.941 rows=432000 loops=1)  Hash Cond: (measurement_data_value.ID = measurement_data_index.ID)  Buffers: shared hit=63537  ->  Seq Scan on measurement_data_value  (cost=0.00..149929.58 rows=8639958 wIDth=16) (actual time=0.004..1095.606 rows=8640000 loops=1)        Buffers: shared hit=63530  ->  Hash  (cost=21.51..21.51 rows=50 wIDth=4) (actual time=0.446..0.446 rows=50 loops=1)        Buckets: 1024  Batches: 1  Memory Usage: 2kB        Buffers: shared hit=7        ->  Hash Join  (cost=1.26..21.51 rows=50 wIDth=4) (actual time=0.015..0.359 rows=50 loops=1)              Hash Cond: (measurement_data_index.fk_parameter_ID = parameter.ID)              Buffers: shared hit=7              ->  Seq Scan on measurement_data_index  (cost=0.00..16.00 rows=1000 wIDth=8) (actual time=0.002..0.135 rows=1000 loops=1)                    Buffers: shared hit=6              ->  Hash  (cost=1.25..1.25 rows=1 wIDth=4) (actual time=0.008..0.008 rows=1 loops=1)                    Buckets: 1024  Batches: 1  Memory Usage: 1kB                    Buffers: shared hit=1                    ->  Seq Scan on parameter  (cost=0.00..1.25 rows=1 wIDth=4) (actual time=0.004..0.007 rows=1 loops=1)                          Filter: ((name)::text = 'param_01'::text)                          Buffers: shared hit=1Total runtime: 2605.277 ms

这个“解决方案”背后的想法是：避免{device,paramater}的单独关键域.这三个只有1000种可能的组合. (可能被认为是BCNF违规的不利情况).所以我把它们组合成一个what_how_where表,它指的是树分开的域.测量(数据)表中的键元素的数量从4减少到2,代替键被省略(因为它不被使用)what_how_where表具有代理键.我的意思可以表示为：如果表中存在一个元组：参数“什么”可以通过设备“如何”在位置“在哪里”来测量.

-- temp schema for scratchDROP SCHEMA tmp CASCADE;CREATE SCHEMA tmp;SET search_path=tmp;        -- tables for the three "key domain"sCREATE table device        ( ID SERIAL NOT NulL PRIMARY KEY,dname VARCHAR NOT NulL -- 'name' might be a reserve word,CONSTRAINT device_name UNIQUE (dname)        );CREATE table parameter        ( ID SERIAL PRIMARY KEY -- must have ID as names are not unique,pname VARCHAR NOT NulL        );CREATE table spot        ( ID SERIAL PRIMARY KEY,sname VARCHAR NOT NulL        );        -- One table to combine the three "key domain"sCREATE table what_how_where        ( ID SERIAL NOT NulL PRIMARY KEY,device_ID INTEGER NOT NulL REFERENCES device(ID),spot_ID INTEGER NOT NulL REFERENCES spot(ID),parameter_ID INTEGER NOT NulL REFERENCES parameter(ID),CONSTRAINT what_natural UNIQUE (device_ID,spot_ID,parameter_ID)        );CREATE table measurement        ( whw_ID INTEGER NOT NulL REFERENCES what_how_where(ID),value VARCHAR(32) NOT NulL,CONSTRAINT measurement_natural PRIMARY KEY (t_stamp,whw_ID)        );INSERT INTO device (dname)SELECT 'dev_' || d::textFROM generate_serIEs(1,10) d;INSERT INTO parameter (pname)SELECT 'param_' || p::textFROM generate_serIEs(1,10) p;INSERT INTO spot (sname)SELECT 'spot_' || s::textFROM generate_serIEs(1,10) s;INSERT INTO what_how_where (device_ID,parameter_ID)SELECT d.ID,s.ID,p.IDFROM device dJOIN spot s ON(1=1)JOIN parameter p ON(1=1)        ;ANALYSE what_how_where;INSERT INTO measurement(whw_ID,value)SELECT w.ID,g,random()::textFROM what_how_where wJOIN generate_serIEs('2012-01-01'::date,'2012-09-23'::date,'1 day'::interval) g        ON (1=1)        ;CREATE UNIQUE INDEX measurement_natural_reversed ON measurement(whw_ID,t_stamp);ANALYSE measurement;        -- A vIEw to *more or less* emulate the original behavIoUrDROP VIEW measurement_data ;CREATE VIEW measurement_data AS (        SELECT d.dname AS dname,p.pname AS pname,w.spot_ID AS spot_ID,w.parameter_ID AS parameter_ID,m.t_stamp AS t_stamp,m.value AS value        FROM measurement m        JOIN what_how_where w ON m.whw_ID = w.ID        JOIN device d ON w.device_ID = d.ID        JOIN parameter p ON w.parameter_ID = p.ID        );EXPLAIN (ANALYZE ON,BUFFERS ON)SELECT md.value  FROM measurement_data md WHERE md.pname = 'param_8'   AND md.t_stamp >= '2012-07-01'   AND md.t_stamp < '2012-08-01'        ;

更新：有一个实际问题,只能通过某种聚类来解决：

>估计行大小为50字节
>并且仅需要参数的5％(1/20)的查询特性
这意味着大约4个“想要的”元组存在于 *** 作系统磁盘页面上(76个不需要的元组)

没有聚类,这意味着所有的页面都必须被检查.索引在这里没有帮助(它们只有在可以避免页面被拉入的情况下才有帮助,这可能是第一个关键列上(范围)搜索的情况)索引可能有助于扫描内存这些页面被抓取后.

因此,这意味着(一旦您的查询的占用空间大于可用缓冲区空间),您的查询实际上会测量机器的I / O速度.

总结

以上是内存溢出为你收集整理的PostgreSQL：如何构建和索引与时间相关的数据以获得最佳查询性能？全部内容，希望文章能够帮你解决PostgreSQL：如何构建和索引与时间相关的数据以获得最佳查询性能？所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。