使用ORDERBY和JSONB資料對本地和外部表進行PostgresFDW連接查詢時速度很慢

我有兩個表，本地表debtors和外部表debtor_registry。我正在使用 PostgreSQL v13。

我的問題是每當我嘗試以下查詢時，獲取 1000 條記錄需要14 秒。

SELECT 
    debtors.id,
    debtors.name,
    debtor_registry.settings
FROM debtors
    INNER JOIN debtor_registry ON debtor_registry.id = debtors.registry_uuid
ORDER BY name LIMIT 1000 OFFSET 0;

我很驚訝，每當我ORDER BY從查詢中洗掉子句時，它變得更快，1000 條記錄只需要194 毫秒。

SELECT 
    debtors.id,
    debtors.name,
    debtor_registry.settings
FROM debtors
    INNER JOIN debtor_registry ON debtor_registry.id = debtors.registry_uuid
LIMIT 1000 OFFSET 0;

此外，另一種情況是，如果我settings從查詢中洗掉JSONB 欄位，并保留該ORDER BY子句。獲得1000條記錄只用了101ms。

SELECT 
    debtors.id,
    debtors.name
FROM debtors
    INNER JOIN debtor_registry ON debtor_registry.id = debtors.registry_uuid
ORDER BY name LIMIT 1000 OFFSET 0;

我懷疑這可能與我試圖獲取多少資料有關。

下面是EXPLAIN ANALYZE VERBOSE結果，如果settingsJSONB領域，ORDER BY name并LIMIT 1000在查詢：

Limit  (cost=114722.78..114725.28 rows=1000 width=57) (actual time=13712.125..14002.827 rows=1000 loops=1)
  Output: debtors.id, debtors.name, debtor_registry.settings
  ->  Sort  (cost=114722.78..114725.63 rows=1140 width=57) (actual time=13703.171..13993.617 rows=1000 loops=1)
        Output: debtors.id, debtors.name, debtor_registry.settings
        Sort Key: debtors.name
        Sort Method: external merge  Disk: 82752kB
        ->  Hash Join  (cost=896.60..114664.90 rows=1140 width=57) (actual time=14.889..917.360 rows=10550 loops=1)
              Output: debtors.id, debtors.name, debtor_registry.settings
              Hash Cond: (((debtor_registry.id)::character varying)::text = (debtors.registry_uuid)::text)
              ->  Foreign Scan on public.debtor_registry  (cost=100.00..113832.74 rows=1137 width=48) (actual time=8.845..902.466 rows=10529 loops=1)
                    Output: debtor_registry.id, debtor_registry.company_id, debtor_registry.settings, debtor_registry.product
                    Remote SQL: SELECT id, settings FROM public.company_debtor
              ->  Hash  (cost=664.60..664.60 rows=10560 width=62) (actual time=6.027..6.028 rows=10554 loops=1)
                    Output: debtors.id, debtors.name, debtors.registry_uuid
                    Buckets: 16384  Batches: 1  Memory Usage: 1108kB
                    ->  Seq Scan on public.debtors  (cost=0.00..664.60 rows=10560 width=62) (actual time=0.019..4.726 rows=10560 loops=1)
                          Output: debtors.id, debtors.name, debtors.registry_uuid
Planning Time: 0.098 ms
JIT:
  Functions: 10
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 1.609 ms, Inlining 0.000 ms, Optimization 0.674 ms, Emission 7.991 ms, Total 10.274 ms
Execution Time: 14007.113 ms

How can I make the 1st query faster without omitting the settings field and the ORDER BY name clause and LIMIT 1000?

UPDATE

1. I also found this similar question but the answer does not solve my problem. Since our sorting is dynamic and we build queries based on the frontend client request.

2. Setting use_remote_estimate to 'true' doesn't help either. :(

uj5u.com熱心網友回復：

嘗試

with t as materialized
(
 SELECT -- your second query as-is
    debtors.id,
    debtors.name,
    debtor_registry.settings
 FROM debtors
    INNER JOIN debtor_registry ON debtor_registry.id = debtors.registry_uuid
 LIMIT 1000 OFFSET 0
)
select * from t ORDER BY name;

即保留快速第二個查詢的計劃并在此之后對結果集進行排序。如果您的 Postgresql 版本在 12 之前，則省略，materialized因為 CTE 總是物化。

第二個建議 - 在本地排序/限制，預先選擇正確的記錄，然后debtor_registry.settings僅提取1000 條記錄。

with t as materialized
(
 SELECT d.id, d.name, d.registry_uuid 
 FROM debtors d
 ORDER BY d.name
 LIMIT 1000 OFFSET 0
)
select t.id, t.name, debtor_registry.settings
FROM t INNER JOIN debtor_registry ON debtor_registry.id = t.registry_uuid
ORDER BY t.name;

標籤：postgresql postgresql-13 postgres-fdw

上一篇：無法從在azurevm上運行的java連接到azurepostgresdb

下一篇：可見性地圖：所有可見頁面