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

On 4/25/19 7:37 AM, Martin Kováčik wrote:
> Hi group,
> 

See comments inline below

> To illustrate my situation let's consider my tests look like this:
> 
> BEGIN;
> 
> -- A: insert data for the test
> 
> -- B: i'll refer to this point later
> 
> -- C: select(s)
> 
> ROLLBACK;
> 
> Everything is fine, until autovacuum (analyze) runs when the test is at 
> point B. After that the query planner at point C chooses wrong plan and 
> the query takes a long time to complete, blocking one CPU core for a 
> long time. It seems like the planner statistics inside running 
> transaction are affected by analyze task running outside of the 
> transaction. In this case after running analyze (outside the 
> transaction) when the transaction is at B, causes query planner to think 
> there are no rows (because the inserts at point A were not yet committed).
> 
> I did prepare a simple test case to reproduce this behavior:
> 
> First you need to create a table:
> 
> create table a (id bigint primary key);
> 
> Then run this transaction:
> 
> begin;
> insert into a
> select * from generate_series(1, 1000);
> 
> -- during sleep execute analyze on this db in separate connection
> select pg_sleep(10);

analyze a;

On my machine that changes the time from:

29715.763 ms

to

291.765 ms

when running ANALYZE in the concurrent connection during the sleep.


> 
> explain analyze select count(*) from (
> select distinct a1, a2, a3, a4 from a a1
> left join a a2 on a1.id <http://a1.id> > 900
> left join a a3 on a2.id <http://a2.id> = a3.id <http://a3.id>
> left join a a4 on a3.id <http://a3.id> = a4.id <http://a4.id>
> left join a a5 on a5.id <http://a5.id> = a4.id <http://a4.id>
> left join a a6 on a6.id <http://a6.id> = a5.id <http://a5.id>
> left join a a7 on a7.id <http://a7.id> = a6.id <http://a6.id>
> left join a a8 on a8.id <http://a8.id> = a7.id <http://a7.id>) temp;
> 
> rollback;
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com