sql - PostgreSQL - 使用索引获取速度非常慢

Question

我在 Centos 6.7 上运行 postgresql 9.4。其中一个表包含数百万条记录，这是 DDL:

CREATE TABLE domain.examples (
  id SERIAL,
  sentence VARCHAR,
  product_id BIGINT,
  site_id INTEGER,
  time_stamp BIGINT,
  category_id INTEGER,
  CONSTRAINT examples_pkey PRIMARY KEY(id)
) 
WITH (oids = false);

CREATE INDEX examples_categories ON domain.examples
  USING btree (category_id);

CREATE INDEX examples_site_idx ON domain.examples
  USING btree (site_id);

使用数据的应用程序使用分页来执行此操作，因此我们正在获取 1000 条记录的批量。然而，即使通过索引列获取，获取时间也非常慢:

explain analyze
select *
from domain.examples e
where e.category_id = 105154
order by id asc 
limit 1000;

Limit  (cost=0.57..331453.23 rows=1000 width=280) (actual time=2248261.276..2248296.600 rows=1000 loops=1)
  ->  Index Scan using examples_pkey on examples e  (cost=0.57..486638470.34 rows=1468199 width=280) (actual time=2248261.269..2248293.705 rows=1000 loops=1)
        Filter: (category_id = 105154)
        Rows Removed by Filter: 173306740
Planning time: 70.821 ms
Execution time: 2248328.457 ms

是什么导致查询缓慢？如何改进？

谢谢!

Answer 1

您可以在 category_id 和 id 两个字段上创建索引:

CREATE INDEX examples_site_idx2 ON domain.examples
  USING btree (category_id, id);

我尝试用您的查询解释分析 3,000,000 行。

使用旧索引:

                                                                  QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..9234.56 rows=1000 width=60) (actual time=0.655..597.193 rows=322 loops=1)
   ->  Index Scan using examples_pkey on examples e  (cost=0.43..138512.43 rows=15000 width=60) (actual time=0.654..597.142 rows=322 loops=1)
         Filter: (category_id = 105154)
         Rows Removed by Filter: 2999678
 Planning time: 2.295 ms
 Execution time: 597.257 ms
(6 rows)

有了新索引:

                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..2585.13 rows=1000 width=60) (actual time=0.027..28.814 rows=322 loops=1)
   ->  Index Scan using examples_site_idx2 on examples e  (cost=0.43..38770.93 rows=15000 width=60) (actual time=0.026..28.777 rows=322 loops=1)
         Index Cond: (category_id = 105154)
 Planning time: 1.471 ms
 Execution time: 28.860 ms
(5 rows)