sql - 优化 Postgresql 查询

Question

我有两个表，我必须查询我的 postgresql 数据库。表 1 有大约 1.4 亿条记录，表 2 有大约 5000 万条记录。

表 1 的结构如下:

tr_id bigint NOT NULL, # this is the primary key
query_id numeric(20,0),       # indexed column
descrip_id numeric(20,0)      # indexed column

表2的结构如下

query_pk  bigint # this is the primary key
query_id  numeric(20,0)    # indexed column
query_token numeric(20,0)

table1 的样本数据库是

1 25 96
2 28 97
3 27 98
4 26 99

table2 的样本数据库是

 1 25 9554
 2 25 9456
 3 25 9785
 4 25 9514
 5 26 7412
 6 26 7433
 7 27 545
 8 27 5789
 9 27 1566
10 28 122
11 28 1456

我更喜欢可以在 tr_id block 中查询的查询。在 10,000 的范围内，因为这是我的要求。

我想以下面的方式得到输出

25  {9554,9456,9785,9514}
26  {7412,7433}
27  {545,5789,1566}
28  {122,1456}

我尝试了以下方式

select query_id, 
       array_agg(query_token) 
from sch.table2 
where query_id in (select query_id 
                   from sch.table1 
                   where tr_id between 90001 and 100000) 
group by query_id 

我正在执行以下查询，大约需要 121346 毫秒，当触发大约 4 个这样的查询时，它仍然需要更长的时间。你能帮我优化一下吗？

我有一台在 Windows 7 上运行的机器，配备 i7 第二代处理器和 8GB 内存。

以下是我的postgresql配置

shared_buffers = 1GB    
effective_cache_size = 5000MB
work_mem = 2000MB

我应该怎么做才能优化它。

谢谢

编辑:如果结果按照以下格式排序就好了

25  {9554,9456,9785,9514}
28  {122,1456}
27  {545,5789,1566}
26  {7412,7433}

即根据 table1 中存在的 queryid 的顺序由 tr_id 排序。如果这在计算上很昂贵，可能在客户端代码中，我会尝试优化它。但我不确定它的效率如何。

谢谢

Answer 1

查询

我希望 JOIN 比您目前拥有的 IN 条件快得多:

SELECT t2.query_id
      ,array_agg(t2.query_token) AS tokens
FROM   t1
JOIN   t2 USING (query_id)
WHERE  t1.tr_id BETWEEN 1 AND 10000
GROUP  BY t1.tr_id, t2.query_id
ORDER  BY t1.tr_id;

这也会根据要求对结果进行排序。 query_token 根据 query_id 保持未排序。

索引

显然，您需要在 t1.tr_id 和 t2.query_id 上建立索引。
你显然已经有了那个:

CREATE INDEX t2_query_id_idx ON t2 (query_id);

t1 上的多列索引可能会提高性能(您必须进行测试):

CREATE INDEX t1_tr_id_query_id_idx ON t1 (tr_id, query_id);

服务器配置

如果这是一个专用的数据库服务器，您可以将 effective_cache_size 的设置提高一些。

@Frank 已经在 work_mem 上给出了建议。我引用 the manual :

Note that for a complex query, several sort or hash operations might be running in parallel; each operation will be allowed to use as much memory as this value specifies before it starts to write data into temporary files. Also, several running sessions could be doing such operations concurrently. Therefore, the total memory used could be many times the value of work_mem;

它应该足够大，能够在 RAM 中对您的查询进行排序。 10 MB 足以一次容纳 10000 行。如果您有一次需要更多的查询，请将其设置得更高。

在专用数据库服务器上有 8 GB，我很想将 shared_buffers 设置为至少 2 GB。

shared_buffers = 2GB    
effective_cache_size = 7000MB
work_mem = 10MB

关于 performance tuning in the Postgres Wiki 的更多建议.