Re: analyze causes query planner to choose suboptimal plan for aselect query in separate transaction - Mailing list pgsql-general

From Michael Lewis
Subject Re: analyze causes query planner to choose suboptimal plan for aselect query in separate transaction
Date
Msg-id CAHOFxGr=CCaQxLoZGFvc+eQ2GWB3huqSk12DZTo+pF_iXNLq3w@mail.gmail.com
Whole thread Raw
In response to Re: analyze causes query planner to choose suboptimal plan for aselect query in separate transaction  (Martin Kováčik <kovacik@redbyte.eu>)
Responses Re: analyze causes query planner to choose suboptimal plan for aselect query in separate transaction  (Martin Kováčik <kovacik@redbyte.eu>)
List pgsql-general


On Thu, Apr 25, 2019, 11:34 AM Martin Kováčik <kovacik@redbyte.eu> wrote:
Turning off autovacuum for the tests is a valid option and I will definitely do this as a workaround. Each test pretty much starts with empty schema and data for it is generated during the run and rolled back at the end. I have a lot of tests and at the moment it is not feasible to modify them.

The real workload for the application is different, but there are some cases, when we import data from remote web service in a transaction do some work with it and then we do a commit. If there is an autovacuum during this process I assume there will be similar problem regarding planner statistics.

Unless you are importing a huge amount of data relative to what is already there, it seems likely to be significantly less impactful than adding data to a completely empty table. The stats on a table with 0 rows and then 5000 rows is going to be night and day, while the difference between stats on 100,000 rows and 105,000 is not as impactful. Musing here. I expect others will chime in.

Stats are not versioned with MVCC so it would expected that a commit in another transaction that is updating stats would influence the query plan for another transaction that is active.

pgsql-general by date:

Previous
From: Martin Kováčik
Date:
Subject: Re: analyze causes query planner to choose suboptimal plan for aselect query in separate transaction
Next
From: Adrian Klaver
Date:
Subject: Re: analyze causes query planner to choose suboptimal plan for aselect query in separate transaction