sql - 在 PostgreSQL 中内部连接表时计数缓慢

Question

我正在使用 postgres 9.4。我已经运行了 VACUUM 和 ANALYZE。但是对 inner join 的查询仍然很慢。

举个简单的例子，我有 3 个表:numbersale、base_number 和 numberstorethrough。 numbersale 和 numberstorethrough 中的 number_id 只是 FK(numbersale.number_id 指向 base_number , numberstorethrough.number_id 指向 numbersale, 是的, 这是可怕的命名):

                                                           Table "public.numbersale"
        Column        |           Type           |                           Modifiers                           | Storage  | Stats target | Description 
----------------------+--------------------------+---------------------------------------------------------------+----------+--------------+-------------
 id                   | integer                  | not null default nextval('numbersale_id_seq'::regclass)       | plain    |              | 
 number_id            | integer                  | not null                                                      | plain    |              | 


                                                        Table "public.base_number"
   Column    |           Type           |                        Modifiers                         | Storage  | Stats target | Description 
-------------+--------------------------+----------------------------------------------------------+----------+--------------+-------------
 id          | integer                  | not null default nextval('base_number_id_seq'::regclass) | plain    |              | 


                                                        Table "public.numberstorethrough"
    Column    |           Type           |                               Modifiers                               | Storage | Stats target | Description 
--------------+--------------------------+-----------------------------------------------------------------------+---------+--------------+-------------
 id           | integer                  | not null default nextval('numberstorethrough_id_seq'::regclass)       | plain   |              | 
 number_id    | integer                  | not null                                                              | plain   |              | 

其中包含 250k 到 595k 的条目:

$ SELECT COUNT(*) FROM numbersale;
 count  
--------
 258552
(1 row)

Time: 17,845 ms

$ SELECT COUNT(*) FROM base_number;
 count  
--------
 332484
(1 row)

Time: 16,273 ms

$ SELECT COUNT(*) FROM numberstorethrough;
 count  
--------
 595812
(1 row)

Time: 56,710 ms

表格有相应的索引:

$ select * from pg_indexes where tablename = 'numbersale';
 schemaname |    tablename     |                 indexname                  | tablespace |                                                              indexdef                                                              
------------+------------------+--------------------------------------------+------------+------------------------------------------------------------------------------------------------------------------------------------
...
public      | numbersale       | numbersale_number_id_key             |            | CREATE UNIQUE INDEX numbersale_number_id_key ON numbersale USING btree (number_id)


$ select * from pg_indexes where tablename = 'numberstorethrough';
 schemaname |        tablename         |               indexname               | tablespace |                                                               indexdef                                                               
------------+--------------------------+---------------------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------
 public     | numberstorethrough       | numberstorethrough_number_id    |            | CREATE INDEX numberstorethrough_number_id ON numberstorethrough USING btree (number_id)

我的问题是以下查询:

SELECT COUNT(*) FROM "numbersale"
INNER JOIN "base_number"
   ON ( "numbersale"."number_id" = "base_number"."id" )
INNER JOIN "numberstorethrough"
  ON ( "numbersale"."id" = "numberstorethrough"."number_id" );
 count  
 --------
 595812
(1 row)

Time: 541,523 ms

解释该查询:

Aggregate  (cost=62564.67..62564.68 rows=1 width=0)
  ->  Hash Join  (cost=34443.31..61075.14 rows=595812 width=0)
        Hash Cond: (numberstorethrough.number_id = numbersale.id)
        ->  Seq Scan on numberstorethrough  (cost=0.00..10539.12 rows=595812 width=4)
        ->  Hash  (cost=30201.41..30201.41 rows=258552 width=4)
              ->  Hash Join  (cost=14411.42..30201.41 rows=258552 width=4)
                    Hash Cond: (base_number.id = numbersale.number_id)
                    ->  Seq Scan on base_number  (cost=0.00..7102.84 rows=332484 width=4)
                    ->  Hash  (cost=10169.52..10169.52 rows=258552 width=8)
                          ->  Seq Scan on numbersale  (cost=0.00..10169.52 rows=258552 width=8)

这种带有两个内部连接的基本查询需要超过半秒(有时需要长达 700 毫秒)，这是否正常？行数甚至不是数百万，只有 300-600k。

我已经简化了我的查询，实际上它更大并且需要超过 1 秒，但连接问题是我的主要瓶颈。

Answer 1

一种可能性是连接产生一个非常大的中间结果，但随后被第二个连接过滤掉。这仍然不能解释为什么没有使用索引，但也许这可能有更好的性能:

SELECT COUNT(*)
FROM "base_number" bn
WHERE EXISTS (SELECT 1 FROM "numbersale" ns WHERE ns."number_id" = bn."id") AND
      EXISTS (SELECT 1 FROM "numberstorethrough" nst WHERE bn."id" = nst."number_id");

您已经拥有此(和您的原始)查询的正确索引:numbersale(number_id)、base_number(id) 和 numberstorethrough(number_id).