I'm not sure if I understand what you mean. My initial thought was that stats are fixed per transaction, i.e. analyze from comitted transaction doesn't interfere with another running transaction. Maybe I was confused by this because analyze can be run inside a transaction, so my assumption was it is isolated like other statements.
On Thu, Apr 25, 2019 at 9:58 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 4/25/19 12:47 PM, Martin Kováčik wrote:
> As my example shows you don't have to import a lot of rows - 1000 is
> enough to make a difference - it all depends on the query. When a
> cartesian product is involved only a few records is enough.
> I think that stats should be MVCC versioned otherwise the planner is
> using wrong statistics and chooses wrong plans.
Then you are looking at moving the choke point to looking up the correct
stats across possibly hundreds/thousands of transactions in flight.
> *Martin Kováčik*
> /CEO/
> *redByte*, s.r.o.
> +421 904 236 791
> kovacik@xxxxxxxxxx <mailto:kovacik@xxxxxxxxxx>, www.redbyte.eu
> <http://redbyte.eu>
>
>
> On Thu, Apr 25, 2019 at 9:28 PM Michael Lewis <mlewis@xxxxxxxxxxx
> <mailto:mlewis@xxxxxxxxxxx>> wrote:
>
>
>
> On Thu, Apr 25, 2019, 11:34 AM Martin Kováčik <kovacik@xxxxxxxxxx
> <mailto:kovacik@xxxxxxxxxx>> 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.
>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
