sql - Postgres 中的多表与表索引

Question

我是一名前端开发人员，几乎没有 SQL 经验。我正在考虑为我工作的组织开发一个数据查询系统。

目前，大部分数据都在一系列电子表格中。我有将近 100 个工作表(即表格)来自相同的模板(包括国家列)，但根据工作表的规划方案(例如“高效”)和经济部门(例如“农业”)具有不同的值。每个工作表大约有 8000 行。

我是否为这些工作表中的每一个创建单独的数据库表 - 即使这些表具有相同的 CREATE 语句？在这种情况下，我想我会按照这些行创建一个索引:

CREATE INDEX sector_scenario_lower_country ON sector_scenario(lower(country));

我必须创建此索引 100 次(每个 sector_scenario 表一次)。当我想找到我正在寻找的数据行时，我必须使用我的应用程序来识别正确的表(这真的应该不会很麻烦或花费很多时间)然后进行查询:

SELECT col4, col5, col6 FROM sector_scenario WHERE lower(country) = "brazil";

或者我应该将方案和部门的列添加到数据库表中，然后将所有工作表复制到该单个表中吗？

在这种情况下，我将只创建一次以下索引:

 CREATE INDEX main_table_idx ON  main_table(scenario, sector, lower(country));

然后我会定期进行以下查询:

SELECT col4, col5, col6 FROM main_table WHERE scenario = "efficient" AND sector = "agriculture" AND lower(country) = "brazil";

显然，第二个选项的设置工作要少得多。但是，我可以期望它具有可比的性能吗？

Answer 1

第二种解决方案是正确的解决方案 -- 将所有行放入一个表中并为该表构建索引。

只有在极少数情况下，您才会将数据分成不同的表。我唯一能想到的是用户要求他们的数据与其他人的数据分开存储。

一个问题是第一种情况下索引的总体大小是否与第二种情况下的大小相当。鉴于第一种情况下的索引平均有半个空页(最后)，我怀疑它们可能更大。存储场景的额外开销每个值只会发生一次。在没有实际测试大小的情况下，我认为数据大小会支持单表方法。

还有其他可能性，在每个表中处理大量数据可能会导致表或索引溢出可用内存。如果这是一个问题，那么打破 table 是一件好事。不过，正确的方法是使用分区将每个段分成一个单独的表——而不是独立管理一堆表。