Re: Strange runtime partition pruning behaviour with 11.4 - Mailing list pgsql-performance
From | MichaelDBA |
---|---|
Subject | Re: Strange runtime partition pruning behaviour with 11.4 |
Date | |
Msg-id | cb89fa89-cef4-8510-2080-040712347b22@sqlexec.com Whole thread Raw |
In response to | Strange runtime partition pruning behaviour with 11.4 (Thomas Kellerer <spam_eater@gmx.net>) |
Responses |
Re: Strange runtime partition pruning behaviour with 11.4
(Andreas Kretschmer <andreas@a-kretschmer.de>)
|
List | pgsql-performance |
I too am a bit perplexed by why runtime partition pruning does not seem to work with this example. Anybody got any ideas of this? Regards, Michael Vitale Thomas Kellerer wrote on 8/2/2019 9:58 AM: > 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 perid. 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 > >
pgsql-performance by date: