Postgres -获取多个表中所有行的最快方法

Question

SELECT table_1.time, table_1.time, table_2.time FROM table_1 
INNER JOIN table_2 on table_1.time = table_2.time 
INNER JOIN table_3 on table_1.time = table_3.time
...;

我使用上面的查询语法来查询多个表中的所有行，连接不同表中的列，然后返回。但是，随着表中行数的增加和表数的增加，性能会大幅度下降。有什么方法可以优化查询性能吗？每个表大约有0.1 -100万行。

我听说过索引、分区和SSD等术语，但我对Postgres还是个新手，不知道该看哪一个。有没有人能提供一些比我目前更好的查询命令语法，或者给出一些关于编辑我的数据库结构的详细建议？

编辑:在加载页面时，获取所有数据只发生一次。因此，我正在尝试加载DB中的所有数据，以可视化绘图。生成初始绘图后，页面将仅查询每个表的最后一行，以更新绘图。表结构非常简单。

Table 1: SPM1

        time         | spm1  |
------------------------------
 2018-09-05 22:23:52 | 43.21 |

Table 2: SPM2
        time         | spm2  |
------------------------------
 2018-09-05 22:23:52 | 43.21 |

..。大约有30张这样的表格

谢谢,

Answer 1

这里有一些基于你所提到的改进的想法。

可以将所有的表转换为三列的表吗？

create table spm (
    id serial primary key,
    time datetime not null,
    spm numeric(5,2) not null,
    number smallint not null
);

insert into spm (time, spm, number)
    values ('2018-09-05 22:23:52', 43.21, 1),
           ('2018-09-05 22:23:52', 43.21, 2)

这将极大地简化查询。Joins很有效，但是20个joins有点多。

select time, spm, number from spm;

加载页面时，

获取所有数据只发生一次。因此，我正在尝试加载DB中的所有数据，以可视化绘图。生成初始绘图后，页面将仅查询每个表的最后一行，以更新绘图。

如果这些绘图被缓存并定期重新生成或更新，那么不仅对于数据库查询，而且对于生成绘图的所有时间，这都将是一个巨大的性能改进。

Answer 2

您尝试过为time字段建立索引吗？您可以像这样创建索引：

CREATE INDEX "table_1_time" ON "table_1" ("time");
CREATE INDEX "table_2_time" ON "table_2" ("time");
CREATE INDEX "table_3_time" ON "table_3" ("time");
... and so on ...

在数据库中运行上述查询一次。创建索引后，添加新数据或删除旧数据将自动建立索引。Indexing makes queries much faster。

编制索引后，请重试查询。但要提醒的是，无论如何，您都是在拉动整个table_1。索引可能对join有所帮助。