使用索引和物化視圖查詢數百萬行仍然很慢

系統有 4 個表，它們連接起來以獲取大量關於使用者的數據，這個查詢變成了一個有 37 列和總共約 800 萬行的視圖。

最終，由於使用者在大約 800 萬行中擁有大約 180 萬行，這變得很慢，所以我決定將它變成一個物化視圖 + 在 user_id 欄位上添加一個索引。

這個物化視圖有一個索引：

create index ix_testing on testing_performance (user_id);

但是即使在 2 分鐘後查詢這個表仍然沒有完成。

這個物化視圖總共約為 4GB。

這是一個簡單的查詢：

select *
from testing_performance tp
where tp.user_id = <user_id>

我們無法減少數據集（因此它必須是所有使用者行，而不是其中的一小部分）。我還沒有找到讓它更快的方法。

解釋分析表明：

Index Scan using ix_testing on public.testing_performance  (cost=0.43..431210.19 rows=1823850 width=527) (actual time=2.488..19089.553 rows=1829111 loops=1)
 Index Cond: (testing_performance.user_id = <user_id>)
 Buffers: shared read=203325 written=4586
Planning Time: 0.103 ms
Execution Time: 19190.872 ms

這是track_io_timing開啟的，但是數據現在已經被記憶體了，所以它比上面的要快得多：

Index Scan using ix_testing on testing_performance bd  (cost=0.43..431210.19 rows=1823850 width=527) (actual time=0.036..1493.887 rows=1829111 loops=1)
  Index Cond: (user_id = <user_id>)
  Buffers: shared hit=3 read=203325 written=11809
  I/O Timings: read=627.655 write=88.767
Planning Time: 1.459 ms
Execution Time: 1585.934 ms

編輯：

帶分頁：

select *
from (
   select
       row_number() over (order by tp.creation_date desc) as rn,
       *
   from testing_performance tp
   where user_id = <user_id>
) x
where (x.rn > 50 * coalesce(0,0) and x.rn <= 50 * (coalesce(0,0) + 1));
-- this is a function, I just replace input parameters with real values for pagination

具有解釋分析的結果（這是一次冷執行，也就是今天第一次執行）：

Subquery Scan on x  (cost=876200.70..935475.83 rows=9119 width=535) (actual time=6822.963..9002.071 rows=50 loops=1)
 Filter: ((x.rn > 0) AND (x.rn <= 50))
 Rows Removed by Filter: 1829061
 Buffers: shared hit=3 read=125267, temp read=115184 written=115190"
 ->  WindowAgg  (cost=876200.70..908118.08 rows=1823850 width=543) (actual time=6822.958..8854.752 rows=1829111 loops=1)
       Buffers: shared hit=3 read=125267, temp read=115184 written=115190"
       ->  Sort  (cost=876200.70..880760.33 rows=1823850 width=535) (actual time=6822.939..7401.405 rows=1829111 loops=1)
             Sort Key: tp.creation_date DESC
             Sort Method: external merge  Disk: 921472kB
             Buffers: shared hit=3 read=125267, temp read=115184 written=115190"
             ->  Index Scan using ix_testing on public.testing_performance tp  (cost=0.43..430943.57 rows=1823850 width=535) (actual time=2.094..4065.285 rows=1829111 loops=1)
                   Index Cond: (tp.user_id = <user_id>)
                   Buffers: shared read=125267
Planning Time: 5.846 ms
Execution Time: 9211.260 ms

第二次執行：

Subquery Scan on x  (cost=872549.65..931824.78 rows=9119 width=535) (actual time=3957.867..5875.673 rows=50 loops=1)
 Filter: ((x.rn > 0) AND (x.rn <= 50))
 Rows Removed by Filter: 1829061
"  Buffers: shared read=125267, temp read=113393 written=113399"
 ->  WindowAgg  (cost=872549.65..904467.03 rows=1823850 width=535) (actual time=3957.864..5745.112 rows=1829111 loops=1)
"        Buffers: shared read=125267, temp read=113393 written=113399"
       ->  Sort  (cost=872549.65..877109.28 rows=1823850 width=527) (actual time=3957.853..4482.528 rows=1829111 loops=1)
             Sort Key: tp.creation_date DESC
             Sort Method: external merge  Disk: 907144kB
"              Buffers: shared read=125267, temp read=113393 written=113399"
             ->  Index Scan using ix_testing on testing_performance tp  (cost=0.43..430943.57 rows=1823850 width=527) (actual time=0.035..1496.060 rows=1829111 loops=1)
                   Index Cond: (user_id = <user_id>)
                   Buffers: shared read=125267
Planning Time: 0.134 ms
Execution Time: 6070.895 ms

您的數據沒有被記憶體，而且您的索引看起來很臃腫。此外，您沒有顯示完整的執行計劃。

此外，從 800 萬過濾 180 萬的索引會加快速度，但可能不會很多。

您應該VACUUM將物化視圖設置為work_mem高。

似乎大部分時間都花在從磁碟讀取許多塊上。如果你能負擔得起執行的停機時間CLUSTER，那可能會加快查詢速度：

CLUSTER testing_performance USING ix_testing;

現在我們有了真正的需求（為使用者返回一頁結果），我們可以提出實現這一目標的方法。

您目前的分頁方法是訪問表中與您的過濾器匹配的所有行，然後對它們進行排序，然後根據使用者所在的頁面返回 50 的範圍。

這是非常有可能快速完成的。您將有一個以適當方式排序的索引，從使用者停止的位置開始讀取索引，並在找到 50 個結果時停止讀取。

為了確保我們不會因為相同creation_date的行而出現任何問題，最好在排序中包含主鍵。我們的索引是

create index ix_testing on testing_performance (user_id, creation_date desc, pk_col);

User_id允許它使用您的過濾器，creation_date並且pk_col您將訂購它們。

您的查詢應該是

select *
from  testing_performance tp
where user_id = <user_id>
order by user_id, creation_date desc , pk_col
limit 50;

對於第一頁。後續頁面有點棘手 - 你需要從你離開的同一點開始閱讀，但你不能只是這樣做

where user_id = <user_id>
and   creation_date <= <blah> 
and   pk_col > <blah>

因為可能存在您想要閱讀的較早creation_date但較低的行，並且您需要過濾器，這樣您就不會將結果行浪費在剛剛閱讀的行上。pk_col``pk_col

這有點複雜，但是，為了解決這個問題（以一種讓查詢計劃器站在你這邊的方式），你可以做兩遍

select * from (
select *
from  testing_performance tp
where user_id = <user_id>
and   creation_date  = timestamp <last result seen creation_date>
and pk_col > <last result seen pk_col>
order by user_id, creation_date  desc , pk_col
limit 50
) e
union all
select * from (
select *
from  testing_performance tp
where user_id = <user_id>
and   creation_date < <last result seen creation_date>
order by user_id, creation_date  desc , pk_col
limit 50
) l                                       
order by user_id, creation_date  desc , pk_col
limit 50

這使您可以繼續從先前到達的行中讀取索引，並且它將在 50 個結果後停止。它的效率有點低，好像它在第一個子查詢中找到 50 行（即你有很多行相同creation_date）它仍然會嘗試找到另外 50 行更小的creation_date. 但是，這仍然比讀取與過濾器匹配的所有行、對它們進行排序然後返回一小部分要好得多——尤其是當使用者每次請求下一頁時，它都必須再次進行所有讀取和排序。

我已經在這個 DB Fiddle中展示了這個方法，即使是一個小的展示結果集，差異也很大（從 96 毫秒下降了 1 毫秒）

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