Postgres刨床JOIN去除

我有兩張表，一張包含很多很少更新的數據，一張包含一些經常更新的數據。第二個表中的每條記錄在第一個表中都有對應的記錄，如下所示：

data_static
----------
id                  | integer                     | not null
....
Indexes:
   "data_pkey" PRIMARY KEY, btree (id)
Referenced by:
   TABLE "data_dynamic" CONSTRAINT "idcheck" FOREIGN KEY (id) REFERENCES data_static(id) ON DELETE CASCADE

表二：

data_dynamic
------------+-----------------------------+-----------
id         | integer                     | not null
...
Indexes:
   "data_dynamic_new_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
   "idcheck" FOREIGN KEY (id) REFERENCES data_static(id) ON DELETE CASCADE

當我在這種情況下（右連接）執行 count with join 時，刨床工作得很好：

explain select count(d.id) from data_static s right join data_dynamic d on s.id = d.id;
                                                    QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=239.10..239.11 rows=1 width=4) (actual time=6.261..6.261 rows=1 loops=1)
  ->  Seq Scan on data_dynamic d  (cost=0.00..207.48 rows=12648 width=4) (actual time=0.013..2.437 rows=10128 loops=1)
Total runtime: 6.311 ms
(3 rows)

但是當我執行內部連接時，它會掃描整個位表，這需要更多時間：

explain select count(d.id) from data_static s inner join data_dynamic d on s.id = d.id;

                                      QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=35530.42..35530.43 rows=1 width=4) (actual time=1629.596..1629.596 rows=1 loops=1)
  ->  Hash Join  (cost=365.58..35498.80 rows=12648 width=4) (actual time=26.418..1625.962 rows=10128 loops=1)
        Hash Cond: (s.id = d.id)
        ->  Seq Scan on data_static s  (cost=0.00..32478.87 rows=252787 width=4) (actual time=14.185..1435.169 rows=252787 loops=1)
        ->  Hash  (cost=207.48..207.48 rows=12648 width=4) (actual time=7.939..7.939 rows=10128 loops=1)
              Buckets: 2048  Batches: 1  Memory Usage: 357kB
              ->  Seq Scan on data_dynamic d  (cost=0.00..207.48 rows=12648 width=4) (actual time=0.012..3.759 rows=10128 loops=1)
Total runtime: 1629.956 ms
(8 rows)

這不是很現實的例子，但我想知道為什麼刨床以這種方式工作以及如何修復它？

如果您在 data_dynamic 中測試 50,000 行，我希望您的內部聯接返回 50,000 行。它似乎返回了大約 10,000 個。有一個外鍵約束——這對我來說似乎很不尋常。

我載入了一堆隨機數據，然後執行它。

analyze data_dynamic;
analyze data_static;
explain analyze 
select count(d.id) from data_static s inner join data_dynamic d on s.id = d.id;

這是我的盒子退回的。(PostgreSQL 9.0.2)

Aggregate  (cost=14941.95..14941.96 rows=1 width=4) (actual time=477.633..477.633 rows=1 loops=1)
 ->  Merge Join  (cost=8409.72..14816.94 rows=50000 width=4) (actual time=237.807..456.682 rows=50000 loops=1)
       Merge Cond: (s.id = d.id)
       ->  Index Scan using data_static_pkey on data_static s  (cost=0.00..91190.58 rows=3000000 width=4) (actual time=0.016..195.279 rows=350001 loops=1)
       ->  Index Scan using data_dynamic_pkey on data_dynamic d  (cost=0.00..2342.23 rows=50000 width=4) (actual time=0.010..60.933 rows=50000 loops=1)
Total runtime: 477.706 ms

所以看起來查詢計劃器能夠為這些表和這些數據量提出一個好的計劃。

這是否意味著您的問題出在硬體或 PostgreSQL 配置上？我不確定它是否那麼簡單。

之後 。. .

我是對的; 它既簡單又不那麼簡單。

基於 PostgreSQL 郵件列表中的這些片段，我要說查詢計劃器根本不支持刪除內部連接。

來自pgsql-performance 郵件列表，2011 年 3 月 2 日

規劃器目前沒有從外鍵約束的存在中進行任何扣除；即使確實如此，我也不確定這是否會幫助它決定可以安全地刪除連接順序約束。

來自pgsql-hackers，2010 年 12 月 12 日

我不會爭辯說在做這樣的事情之前不需要仔細分析 - 特別是，如果我們曾經移除內部連接，我仍然希望在某個時候這樣做，. . .

引用自：https://dba.stackexchange.com/questions/6121