Strange runtime partition pruning behaviour with 11.4

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Strange runtime partition pruning behaviour with 11.4
Date: 2019-08-02 13:58:51
Message-ID: 29b2f4c6-987b-d364-2e1d-bbd479d06da2@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message MichaelDBA 2019-08-03 13:16:22 Re: Strange runtime partition pruning behaviour with 11.4
Previous Message Imre Samu 2019-08-02 11:04:10 Re: PSQL performance - TPS