Re: BUG #16280: dead tuples (probably) effect plan and query performance - Mailing list pgsql-bugs

From Serbin, Ilya
Subject Re: BUG #16280: dead tuples (probably) effect plan and query performance
Date
Msg-id CALTXVii9X0Dndas0W63E5eA6h4+Q24JaJ4VTfqg0GXbD1fk8ng@mail.gmail.com
Whole thread Raw
In response to Re: BUG #16280: dead tuples (probably) effect plan and query performance  (James Coleman <jtc331@gmail.com>)
List 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.

pgsql-bugs by date:

Previous
From: PG Bug reporting form
Date:
Subject: BUG #16306: cluster error
Next
From: Horimoto Yasuhiro
Date:
Subject: Re: BUG #16303: A condtion whether an index-only scan is possibleincludes a wrong