Postgres：应该在填充表之前还是之后创建索引 [英] Postgres: should one create indexes before or after populating tables
问题描述
我正在建立一个包含小时表的数据库。每个表都在单个copy语句中创建。这些表的目的是保存标准文件中保存的数据的索引摘要，该文件的压缩效果很好。为了记录这些文件，它们保存了Internet流数据。
有时，我们需要搜索一个月以上的文件，以查找特定的地址，并且一次浏览一个文件需要很长时间。时间，目的是为每个小时文件建立一个索引，列出该小时的IP地址。要进行搜索，我们会依次浏览感兴趣的时间范围，然后每小时进行一次快速搜索，以查看是否需要解压缩并搜索磁盘上的相应文件。
我正在使用Postgres，因为它本身支持IP地址。因此，对于每个小时，我都会创建一个表，该表的名称与磁盘上的相应文件有关（日期和时间，令人惊讶）。我需要用于源地址和目标地址的索引，但是我应该在使用copy语句加载数据之前或之后创建这些索引。
我想另一种放置方式是
一旦加载了表，通常将永远不会对其进行修改。直到被删除。
您使用的是哪个版本的Postgres？
您是否考虑过分区？
是的，在结尾处建立索引应该更快，但这会留下很多细微差别。 / p>
I am setting up a database which has hourly tables. Each table is created in a single copy statement. The purpose of the tables is to hold an indexed summary of the data held in standard files that compress really well. For the record these files hold Internet flow data.
Sometimes we need to search over a months files looking for specific addresses and working through the files one at a time takes a long time so the intention is to build an index of each hour file which lists that IP Addresses for that hour. To search we go serially through the time range we are interested in and do a quick search on each hour to see if we need to unpack and search the corresponding file on disk.
I am using Postgres since it supports IP addresses natively. So for each hour I create a table with a name related to the corresponding file on disk (date and time, surprise). I need indexes for the source and destination addresses but should I create those indexes before or after I load the data using a copy statement.
I guess another way of putting it is whether it is faster to do the indexing when the data is being loaded or in one go after it is all done.
Once a table is loaded it will normally never be modified until it is deleted.
What version of Postgres are you on?
Have you considered partitioning?
And, yes, it ought to be faster to build the index at the end, but that leaves out a lot of nuance.
这篇关于Postgres：应该在填充表之前还是之后创建索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
