To: pgsql-performance@lists.postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Strange runtime partition pruning behaviour with 11.4
Message-ID: <29b2f4c6-987b-d364-2e1d-bbd479d06da2@gmx.net>
Date: Fri, 2 Aug 2019 15:58:51 +0200
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.23)
 Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Language: en-GB
Content-Transfer-Encoding: 7bit
Precedence: bulk

I stumbled across this question on SO: https://stackoverflow.com/questions/56517852

Disregarding the part about Postgres 9.3, the example for Postgres 11 looks a bit confusing. 

There is a script to setup test data in that question: 

==== start of script ====

    create table foo (
        foo_id integer not null,
        foo_name varchar(10),
        constraint foo_pkey primary key (foo_id) 
    );      

    insert into foo
      (foo_id, foo_name) 
    values
      (1, 'eeny'),
      (2, 'meeny'),
      (3, 'miny'),
      (4, 'moe'),
      (5, 'tiger'), 
      (6, 'toe');

    create table foo_bar_baz (
        foo_id integer not null,
        bar_id integer not null,
        baz    integer not null,
        constraint foo_bar_baz_pkey primary key (foo_id, bar_id, baz),
        constraint foo_bar_baz_fkey1 foreign key (foo_id)
            references foo (foo_id)
    ) partition by range (foo_id) 
    ;

    create table if not exists foo_bar_baz_0 partition of foo_bar_baz for values from (0) to (1);
    create table if not exists foo_bar_baz_1 partition of foo_bar_baz for values from (1) to (2);
    create table if not exists foo_bar_baz_2 partition of foo_bar_baz for values from (2) to (3);
    create table if not exists foo_bar_baz_3 partition of foo_bar_baz for values from (3) to (4);
    create table if not exists foo_bar_baz_4 partition of foo_bar_baz for values from (4) to (5);
    create table if not exists foo_bar_baz_5 partition of foo_bar_baz for values from (5) to (6);

    with foos_and_bars as (
        select ((random() * 4) + 1)::int as foo_id, bar_id::int
        from generate_series(0, 1499) as t(bar_id)
    ), bazzes as (
        select baz::int
        from generate_series(1, 1500) as t(baz)
    )
    insert into foo_bar_baz (foo_id, bar_id, baz) 
    select foo_id, bar_id, baz 
    from bazzes as bz 
      join foos_and_bars as fab on mod(bz.baz, fab.foo_id) = 0;

==== end of script ====

I see the some strange behaviour similar to to what is reported in the comments to that question: 

When I run the test query immediately after populating the tables with the sample data:

    explain analyze 
    select count(*) 
    from foo_bar_baz as fbb 
      join foo on fbb.foo_id = foo.foo_id 
    where foo.foo_name = 'eeny'

I do see an "Index Only Scan .... (never executed)" in the plan for the irrelevant partitions: 

  https://explain.depesz.com/s/AqlE

However once I run "analyze foo_bar_baz" (or "vacuum analyze"), Postgres chooses to do a "Parallel Seq Scan" for each partition:

  https://explain.depesz.com/s/WwxE

Why does updating the statistics mess up (runtime) partition pruning? 


I played around with random_page_cost and that didn't change anything. 
I tried to create extended statistics on "foo(id, name)" so that the planner would no, that there is only one name per id. No change. 

I saw the above behaviour when running this on Windows 10 (my Laptop) or CentOS 7 (a test environment on a VM) 

On the CentOS server default_statistics_target is set to 100, on my laptop it is set to 1000

In both cases the Postgres version was 11.4

Any ideas? 

Thomas