Re: BUG #16280: dead tuples (probably) effect plan and query performance

From: "Serbin, Ilya" <iserbin(at)bostonsd(dot)ru>
To: James Coleman <jtc331(at)gmail(dot)com>
Cc: PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: BUG #16280: dead tuples (probably) effect plan and query performance
Date: 2020-03-17 17:36:47
Message-ID: CALTXVii9X0Dndas0W63E5eA6h4+Q24JaJ4VTfqg0GXbD1fk8ng@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

It seems like I found the reason. I've recreated the "content_idx" gin
(content jsonb_path_ops) with fastupdate=false, and good plan stayed even
on deadtuples = 4000+ ( as I said previously good plan changed to bad
starting on ~300 dead tuples in table).
So, as far as I understand the issue raises once gin pending-entry list
reaches ~300 entries. AFAIK gin pending-entry list is stored unsorted, so
optimizer may decide to choose another plan to be used since cost
estimations for scanning unsorted gin pending-entry list may be to high.
Once I disabled fastupdate mechanism I've raised overhead for write
operations, but scanning uses only index body, without gin pending-entry
(since it's not presented anymore).
Would much appreciate if someone can confirm or disprove my conclusions.

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Horimoto Yasuhiro 2020-03-18 00:34:31 Re: BUG #16303: A condtion whether an index-only scan is possible includes a wrong
Previous Message PG Bug reporting form 2020-03-17 15:33:11 BUG #16306: cluster error