我有一個有 2 個端點的 Flask API。

當使用者點擊一個按鈕時，前端首先呼叫一個/calculate路由，該路由計算一個昂貴的查詢並將結果附加到一個名為results.

該表的索引如下：

create index if not exists results_tbl_idx on results
             (customer_id, result_id, team_id, team_name, territory_name, specialty, id);

返回時/calculate，它會獲取 的句柄result_id，然後呼叫/get_results端點，該端點在表上執行以下查詢results：

with distinct_users_team_tbl as (
   select team_id,
       count(distinct id) as distinct_users_team,
       count(distinct id) filter ( where targeted_users > 0 ) as targeted_distinct_users_team
   from tam_results_14
   where result_id = 201 and customer_id = '1'
   group by team_id
),
distinct_users_territory_tbl as (
   select team_id, territory_name,
       count(distinct id) as distinct_users_territory,
       count(distinct id) filter ( where targeted_users > 0 ) as targeted_distinct_users_territory
   from tam_results_14
   where result_id = 201 and customer_id = '1'
   group by team_id, territory_name
),
distinct_users_specialty_tbl as (
   select team_id, team_name, territory_name, specialty,
       sum(tam) as tam,
       sum(sam) as sam,
       count(distinct id) as  distinct_users_specialty,
       count(distinct id) filter ( where targeted_users > 0 ) as  targeted_distinct_users_specialty
   from tam_results_14
   where result_id = 201 and customer_id = '1'
   group by team_id, team_name, territory_name, specialty
)

select *
from distinct_users_specialty_tbl
join distinct_users_team_tbl
   on distinct_users_specialty_tbl.team_id = distinct_users_team_tbl.team_id
join distinct_users_territory_tbl
   on distinct_users_specialty_tbl.team_id = distinct_users_territory_tbl.team_id
   and distinct_users_specialty_tbl.territory_name = distinct_users_territory_tbl.territory_name

有時，在執行之後/calculate，當獲取時/get_results，查詢永遠掛起。

通常，/get_results查詢在 < 4 秒內執行。即使它掛起，我也可以將掛起的完全相同的查詢粘貼到控制台並在 < 4 秒內執行它。

我已經檢查pg_stat_activity並可以看到查詢執行時沒有wait_event和state = active。

然後我檢查pg_locks了一下pid，我可以看到：

我現在的預感

results在我查詢之前，數據庫是否還沒有完成對錶的新條目的索引？但是，當我將掛起的查詢粘貼到終端並執行哪個可以使用索引時，它已經完成向索引添加項目？

有什麼方法可以在執行/get_results查詢之前強製表完全索引？這甚至可能嗎？

謝謝你的幫助！！！

添加顯式

ANALYZE results;

最後，\calculate使其他函式具有準確的表統計資訊。

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