Re: analyze causes query planner to choose suboptimal plan for aselect query in separate transaction

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@redbyte.eu <mailto:kovacik@redbyte.eu>, www.redbyte.eu
> <http://redbyte.eu>
>
>
> On Thu, Apr 25, 2019 at 9:28 PM Michael Lewis <mlewis@entrata.com
> <mailto:mlewis@entrata.com>> wrote:
>
>
>
>     On Thu, Apr 25, 2019, 11:34 AM Martin Kováčik <kovacik@redbyte.eu
>     <mailto: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.
>


--
Adrian Klaver
adrian.klaver@aklaver.com