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

我用的是Postgres9.4。我已经跑了VACUUM和ANALYZE。但对inner join的查询仍然很慢。
例如，我有3个表：numbersale，base_number和numberstorethrough。number_id中的numbersale和numberstorethrough只是FKs（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秒以上的时间，但是连接的问题是我的主要瓶颈。

最佳答案

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

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)。