对8000万行执行SELECT操作耗时过长

Question

表格：

                       Table "public.hugetable"
 Column  |         Type          | Modifiers | Storage  | Description 
---------+-----------------------+-----------+----------+-------------
 reqid   | character varying(15) |           | extended | 
 browser | character varying(15) |           | extended | 
 a       | smallint              |           | plain    | 
 b       | smallint              |           | plain    | 
 metarr  | smallint[]            |           | extended | 

行数：8000万

索引：无

解释：

testdb=> EXPLAIN (ANALYZE,BUFFERS) select b from hugetable;
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on hugetable  (cost=0.00..6514286.08 rows=80000008 width=2) (actual time=0.009..598004.456 rows=80000000 loops=1)
   Buffers: shared hit=472831 read=5241455
 Total runtime: 674134.766 ms

CCD_ 1包含250个元素。

使用select b from hugetable where a=someval或select metric[199] from hugetable 进行查询所花费的时间相似

服务器规格：

db.m3.xlarge
Type:Type   Standard - Current Generation
vCPU:Number of virtual cores    4 vCPU
Memory: 15 GiB

---

我从未使用过如此大的数据集，所以我不确定10分钟对于这种查询是否正常。

在实践中，会有另一列（日期时间）。该表将在一整天内包含约8000万条记录，查询将始终为SELECT metarr[someindex] from hugetable where datetimecolumn > something and datetimecolumn <something。

我能做些什么让它更快？似乎一旦我添加了一个日期时间列并查询了一段时间，仍然需要花费大量的时间！

Answer 1

磁盘上的表比您的RAM大。因此，该表不能完全缓存在RAM中。增加RAM大小以适应您的工作集。这将删除所有IO.

只为您需要的列编制索引。这样PostgreSQL就可以执行仅索引的扫描，而扫描的数据要少得多。这减少了工作集。

您现在可能会在数据库和应用程序之间的网络上遇到瓶颈。

Answer 2

如何提高数据库查询性能

1. 为用于比较的列编制索引（例如：日期列在两个示例中都应编制索引）

   SELECT * FROM X WHERE X.date = 2014
   

   或

   SELECT * FROM X INNER JOIN Y ON X.date = Y.date
   

2. 将表水平分区（shard）为几个块（例如：每日轮换），并将每个块放在不同的机器上。

3. 通过在多台机器上复制数据库分区来复制数据库。这对你的情况没有多大帮助，除非你有不同标准的并发查询

4. 使用高IOPS SSD驱动器（AWS称之为预配IO）。

5. 对于使用日期时间类型的日期，它的性能优于inthttp://gpshumano.blogs.dri.pt/2009/07/06/mysql-datetime-vs-timestamp-vs-int-performance-and-benchmarking-with-myisam/

6. 使用memcached、redis、。。以缓存结果，以便更快地检索（如果同一查询将运行多次）