首頁>Database>source

我们遇到两个表之間的聯接查詢問题(仅顯示查詢中的相關列):

CREATE TABLE IF NOT EXISTS payments (
    id TEXT PRIMARY KEY,
    consumer_id TEXT,
    created_at TIMESTAMP WITHOUT TIME ZONE,
    status TEXT,
    tier TEXT,
    number_of_installments INTEGER,
    authorization_result TEXT,
    authorization_details JSONB,
    last_updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL
);

assessmentreports 使用宣告性分區對表进行分區,该宣告性分區在所有分區上均具有以下結構和索引:

CREATE TABLE IF NOT EXISTS assessmentreports (
    id TEXT PRIMARY KEY,
    payment_id TEXT,
    kind TEXT,
    created_at TIMESTAMP WITHOUT TIME ZONE,
    result TEXT,
    metadata JSONB,
    last_updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL
) PARTITION BY RANGE (created_at);
CREATE INDEX assessmentreports_kind_result ON public.assessmentreports USING btree (kind, result)
CREATE INDEX assessmentreports_payment_id ON public.assessmentreports USING btree (payment_id)
CREATE UNIQUE INDEX assessmentreports_pkey ON public.assessmentreports USING btree (created_at, id)
CREATE INDEX assessmentreports_last_updated_at_idx ON public.assessmentreports USING brin (last_updated_at)

assessmentreports中可以有多个條目 對於一个 payment_id 每个都有不同的 kind .不幸的是,可能有多个同一个 對於给定的 kind

以下是一个典型示例:

payment_id

如果我们將時間視窗保持在2个小時,則計划程式会在 SELECT payments.id, status, consumer_id, tier, authorization_result, authorization_details, number_of_installments, max(result) FILTER (WHERE (kind = 'credit_check')) AS credit_result, max(result) FILTER (WHERE (kind = 'new_credit_check')) AS new_check_result, max(result) FILTER (WHERE (kind = 'address_assessment')) AS reject_inference_result, max(metadata ->> 'credit_record_id') FILTER (WHERE (kind = 'new_credit_check')) AS new_credit_record_id, max(metadata ->> 'credit_record_id') FILTER (WHERE (kind = 'credit_check')) AS credit_record_id FROM payments LEFT JOIN assessmentreports ON assessmentreports.payment_id = payments.id AND kind IN ('credit_check', 'new_credit_check', 'address_assessment') AND assessmentreports.created_at < now() -- To remove future partitions from plan WHERE payments.last_updated >= now() - '2 hours'::INTERVAL GROUP BY 1上顯示索引掃描 而且速度非常快(2秒).請註意,我已经删除了分區的节點,因為它们看起来都一樣。

payment_id = id

在2小時內甚至增加一點時間時,即使 GroupAggregate (cost=6231334.74..6231477.18 rows=2187 width=305) Group Key: payments.id -> Sort (cost=6231334.74..6231343.35 rows=3445 width=467) Sort Key: payments.id -> Nested Loop Left Join (cost=0.99..6231132.34 rows=3445 width=467) -> Index Scan using payments_last_updated on payments (cost=0.56..1801.97 rows=2187 width=145) Index Cond: (last_updated >= '2020-12-25 13:57:26.927564'::timestamp without time zone) -> Append (cost=0.43..2846.99 rows=135 width=344) -> Index Scan using assessmentreports_2016_payment_id on assessmentreports_2016 (cost=0.43..14.47 rows=1 width=181) Index Cond: (payment_id = payments.id) Filter: ((created_at < '2020-12-25 15:57:26.927564'::timestamp without time zone) AND (kind = ANY ('{credit_check,new_credit_check,address_assessment}'::text[]))) -> Index Scan using assessmentreports_201701_payment_id on assessmentreports_201701 (cost=0.42..13.39 rows=1 width=161) Index Cond: (payment_id = payments.id) Filter: ((created_at < '2020-12-25 15:57:26.927564'::timestamp without time zone) AND (kind = ANY ('{credit_check,new_credit_check,address_assessment}'::text[]))) -> Index Scan using assessmentreports_201702_payment_id on assessmentreports_201702 (cost=0.43..17.74 rows=1 width=192) Index Cond: (payment_id = payments.id) Filter: ((created_at < '2020-12-25 15:57:26.927564'::timestamp without time zone) AND (kind = ANY ('{credit_check,new_credit_check,address_assessment}'::text[])))中的行數增加,計划也会完全改變,並且效能会降低几个數量級(+40分钟) 在 payments之後 過濾器没有增加太多.再次删除分區的节點,因為它们看起来都一樣:

last_updated_at

我對第一个計划的理解是它正在過濾 GroupAggregate (cost=35839796.76..35841181.68 rows=21288 width=304) Group Key: payments.id -> Sort (cost=35839796.76..35839880.48 rows=33487 width=463) Sort Key: payments.id -> Hash Join (cost=25622.81..35830297.49 rows=33487 width=463) Hash Cond: (assessmentreports_2016.payment_id = payments.id) -> Append (cost=7457.32..35712704.68 rows=37877067 width=341) -> Bitmap Heap Scan on assessmentreports_2016 (cost=7457.32..181237.37 rows=228085 width=181) Recheck Cond: (kind = ANY ('{credit_check,new_credit_check,address_assessment}'::text[])) Filter: (created_at <= '2020-12-25 17:02:52.008321'::timestamp without time zone) -> Bitmap Index Scan on assessmentreports_2016_kind_result (cost=0.00..7400.30 rows=228085 width=0) Index Cond: (kind = ANY ('{credit_check,new_credit_check,address_assessment}'::text[])) -> Bitmap Heap Scan on assessmentreports_201701 (cost=4018.67..32291.56 rows=130762 width=161) Recheck Cond: (kind = ANY ('{credit_check,new_credit_check,address_assessment}'::text[])) Filter: (created_at <= '2020-12-25 17:02:52.008321'::timestamp without time zone) -> Bitmap Index Scan on assessmentreports_201701_kind_result (cost=0.00..3985.98 rows=130762 width=0) Index Cond: (kind = ANY ('{credit_check,new_credit_check,address_assessment}'::text[])) -> Hash (cost=17899.39..17899.39 rows=21288 width=144) -> Index Scan using payments_last_updated on payments (cost=0.56..17899.39 rows=21288 width=144) Index Cond: (last_updated >= '2020-12-25 07:02:52.008321'::timestamp without time zone)assessmentreports 经過 payments.id = payment_id筛選之後 由 payments .對我而言,這天真地讲得通,因為与仅使用實物過濾器相比,這種方法可以减少很多付款。

到目前為止我们尝試過的事情 last_updated_at
  • The RANDOM_PAGE_COST is set to 4 but should be 1.1 or 1 since we are on SSDs. We try to set it for the transaction and that seems to extend the usage of the index up to a window of . 10小時的視窗在4秒內完成,而"位圖"計划在10分钟後没有完成。
  • 為(payment_id和kind)建立自定義依赖項統計資訊,但這似乎並没有真正作用。

有什麼想法吗? 我们在正確的轨道上以為 10 hours 計划對於這些資料量更好,而只是PostgreSQL感到困惑? 在查看了payment_id列的統計資訊之後,我看不到任何可以帮助計划者了解對 index scan进行多少過濾的資訊 会减少行數,但似乎很了解實物過濾会做什麼。

近似統計

為了避免在此處共享太多資訊,我必须故意含糊。

  1. 評估報告(所有分區)中的行总數远超過10亿,大小為202Gb。
  2. 種類的數量隨着時間的推移而增加,現在已经超過20種。
  3. 每次付款的評估報告的數量取決於付款,但是對於绝大多數情况,全部20多種都存在。
  4. 付款的發佈大小為33Gb,並且没有分區。
硬體

我们正在具有16个vCPU,48GB RAM和SSD的RDS上執行PostgreSQL 12(無Redshift)。

payment_id
最新回復
  • 5月前
    1 #

    索引

    查詢計划溢位您具有索引 payments_last_updated .這就是我们需要的所有

    關於 payments

    assessmentreports

    There can be multiple entries in 對於一个 assessmentreports 每个都有不同的種類。

    所以可能(應该)有這个​​唯一索引:

    payment_id
    

    這應该有助於进行索引掃描。

    查詢

    我会尝試將聚合向下移動到 CREATE UNIQUE INDEX assessmentreports_payment_id_kind_uni ON assessmentreports (payment_id, kind); 子查詢.除了上面新的(?)量身定製的索引之外,這還應该支援索引掃描.同時儲存外部聚合。

    LATERAL
    
    設置

    SELECT p.* -- or your list of payments columns , a.* FROM payments p LEFT JOIN LATERAL ( SELECT max(result) FILTER (WHERE kind = 'credit_check') AS credit_result , max(result) FILTER (WHERE kind = 'new_credit_check') AS new_check_result , max(result) FILTER (WHERE kind = 'address_assessment') AS reject_inference_result , max(metadata ->> 'credit_record_id') FILTER (WHERE kind = 'new_credit_check') AS new_credit_record_id , max(metadata ->> 'credit_record_id') FILTER (WHERE kind = 'credit_check') AS credit_record_id FROM assessmentreports WHERE payment_id = p.id AND kind IN ('credit_check', 'new_credit_check', 'address_assessment') AND created_at < now() ) a ON true WHERE p.last_updated_at >= now() - interval '2 hours';中超過500,000,000行 .但是你用 assessmentreports操作 PK和FK? 通常比 text贵 (或 integer 如果必须的话) 和處理。

    通過對表列重新排序可能会获得更多好處.參见:

    bigint

    storage 對於该基數表尤其可疑.尽可能使用专用列. (空間)效率更高.列中的NULL儲存非常便宜.參见:

      Calculating and saving space in PostgreSQL

    48 GB的RAM可能不足以保留大部分資料. (如果您浪费更少的儲存空間,則更多。) metadata JSONB 即使使用SSD儲存,也應至少為1.1。

    此索引看起来很奇怪:

      How do I select by a JSONB array containing at least one element within a range?

      How do completely empty columns in a large table affect performance?

    請不要將唯一索引稱為" pkey".只有PK索引應该這樣命名.為何在 上拥有唯一索引 首先開始?

    所有常規效能建議均適用。 特別是: random_page_cost CREATE UNIQUE INDEX assessmentreports_pkey ON assessmentreports USING btree (created_at, id); .設置高.類似:

    (created_at, id)
    

    手册:

    最後,我看不到 effective_cache_size 在您的查詢計划中.可能会對您的口徑表有所帮助.您配置正確吗? 手册:

    effective_cache_size = 36GB

    a higher value makes it more likely index scans will be used

    parallelism 計划节點.這个 通常在實現

    Whenever PostgreSQL needs to combine rows from multiple sources into a single result set, it uses an 時發生 或掃描 分區表.這樣的节點可以在並行計划中使用,就像 他们可以在任何其他計划中使用.但是,在並行計划中,計划者 可以改用 Append 节點。

    MergeAppend

  • mysql:我如何命名在查詢的FROM部分完成的事情?
  • postgresql:為什麼我的RDS在一段時間後讀取IOPS下降?