Runtime partition pruning with hash partitioning
Hello, Runtime partition pruning don't work without index on a hash partitioned column. Consider this test case on version 12: create table data ( key_id integer not null, value real not null ) partition by hash(key_id); create table data_0 partition of data for values with (modulus 3, remainder 0); create table data_1 partition of data for values with (modulus 3, remainder 1); create table data_2 partition of data for values with (modulus 3, remainder 2); insert into data (key_id, value) select floor(random() * 100), random() from generate_series(0, 100) as g (i); vacuum analyze data; explain (analyze, settings, costs off, timing off, summary off) with keys(id) as ( values (1),(2) ) select * from data join keys on (data.key_id = keys.id); QUERY PLAN Gather (actual rows=19845 loops=1) Workers Planned: 2 Workers Launched: 2 -> Hash Join (actual rows=6615 loops=3) Hash Cond: (data_2.key_id = "*VALUES*".column1) -> Parallel Append (actual rows=34 loops=3) -> Parallel Seq Scan on data_2 (actual rows=126670 loops=3) -> Parallel Seq Scan on data_1 (actual rows=160458 loops=2) -> Parallel Seq Scan on data_0 (actual rows=299075 loops=1) -> Hash (actual rows=2 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 5kB -> Values Scan on "*VALUES*" (actual rows=2 loops=3) We see that all partitions scanned. But after creating index postgres actually looks only to selected partitions: create index on data(key_id); explain (analyze, settings, costs off, timing off, summary off) with keys(id) as ( values (1),(2) ) select * from data join keys on (data.key_id = keys.id); QUERY PLAN - Nested Loop (actual rows=19845 loops=1) -> Values Scan on "*VALUES*" (actual rows=2 loops=1) -> Append (actual rows=9922 loops=2) -> Bitmap Heap Scan on data_0 (actual rows=9926 loops=1) Recheck Cond: (key_id = "*VALUES*".column1) Heap Blocks: exact=1324 -> Bitmap Index Scan on data_0_key_id_idx (actual rows=9926 loops=1) Index Cond: (key_id = "*VALUES*".column1) -> Bitmap Heap Scan on data_1 (never executed) Recheck Cond: (key_id = "*VALUES*".column1) -> Bitmap Index Scan on data_1_key_id_idx (never executed) Index Cond: (key_id = "*VALUES*".column1) -> Bitmap Heap Scan on data_2 (actual rows=9919 loops=1) Recheck Cond: (key_id = "*VALUES*".column1) Heap Blocks: exact=1679 -> Bitmap Index Scan on data_2_key_id_idx (actual rows=9919 loops=1) Index Cond: (key_id = "*VALUES*".column1) Why runtime partition pruning needs index? Is it intended behavior? -- - Pavel Luzanov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
Re: [EXTERNAL]: Re: pglogical install errors openSUSE Leap 42.1
Hi, I'm not an OpenSuse expert, but I know a bit about building pglogical... I've been able to compile pglogical on a leap 42.3 docker image In the beginning, I tried to compile pglogical using the very same postgresql packages you were using, but for some reasons, I was consistently receiving linking errors. After some debugging, and packages digging, I found some official SUSE packages for leap 42 So I decided to give them a try. Here is the URL to the repo: https://download.opensuse.org/repositories/openSUSE:/Leap:/42.3:/Update/ports/openSUSE:Leap:42.3:Update.repo Note: here https://download.opensuse.org/repositories/openSUSE:/Leap:/ you can find also repositories for older leap versions. including 42.1 An important suggestion I can give you is: please use a checkout from git instead of using a tar file containing the source code. This because pglogical uses a git submodule for the pglogical_dump component. Here is the link to the 2ndquadrant source repository: https://github.com/2ndQuadrant/pglogical Here is the sequence of action I performed: zypper addrepo https://download.opensuse.org/repositories/openSUSE:/Leap:/42.3:/Update/ports/openSUSE:Leap:42.3:Update.repo zypper install postgresql94-devel openssl-devel libxml2-devel libxslt-devel pam-devel readline-devel zlib-devel krb5-devel e2fsprogs-devel libselinux-devel git vim then I cloned the pglogical git: git clone https://github.com/2ndQuadrant/pglogical.git (note, I used the https link because in docker I was having some issues using the ssh protocol. if you face the same issue update the .gitmodule file inside the repository checkout to use the https link to pglogical_dump https://github.com/2ndQuadrant/pglogical_dump.git ) git checkout REL2_3_0 (this because I wanted to use the latest released code. feel free to use another tag if you wish) git submodule sync git submodule init git submodule update now the repository is up to date. PATH=/usr/lib/postgresql94/bin:$PATH make USE_PGXS=1 clean all PATH=/usr/lib/postgresql94/bin:$PATH make USE_PGXS=1 install (as you can notice the path is different from the documentation as suse uses different default paths from rhel and debian) This worked for me on OpenSUSE leap 42.3, PLEASE let me know if it works for you too. Ciao, Giulio On Tue, 18 Feb 2020 at 23:07, Bellrose, Brian wrote: > > I found a repository that offered it and added the repo. > https://download.opensuse.org/repositories/home:vjt:ifad/openSUSE_Leap_42.1/home:vjt:ifad.repo > > Only time will tell if that was a wise decision. I only need to it to get me > through my upgrade. Moving all these to RHEL 8.1 and Postgres 11.7. Going to > use pglogical to try and reduce downtime. > > Brian > > -Original Message- > From: Adrian Klaver > Sent: Tuesday, February 18, 2020 5:00 PM > To: Bellrose, Brian ; > pgsql-general@lists.postgresql.org > Subject: Re: [EXTERNAL]: Re: pglogical install errors openSUSE Leap 42.1 > > On 2/18/20 1:56 PM, Bellrose, Brian wrote: > > Yes, > > > > S | Name | Summary > > | Type > > --+---+-+ > > i | postgresql94 | Basic Clients and Utilities for PostgreSQL > > | package > > i | postgresql94-contrib | Contributed Extensions and Additions to > > PostgreSQL | package > > i | postgresql94-devel| PostgreSQL development header files and > > libraries | package > > i | postgresql94-plperl | The PL/Tcl, PL/Perl, and PL/Python procedural > > languages for PostgreSQL | package > > i | postgresql94-plpython | The PL/Python Procedural Languages for > > PostgreSQL | package > > i | postgresql94-pltcl| PL/Tcl Procedural Language for PostgreSQL > > | package > > i | postgresql94-server | The Programs Needed to Create and Run a > > PostgreSQL Server | package > > i | postgresql94-test | The test suite for PostgreSQL > > | packagep > > Alright that looks good. You may still need to play Whack-a-Mole with devel > libraries. > > I am curious how you got to 9.4.25 via an update, given that this Postgres > version was released well past the OS EOL? > > > > > Brian > > > > -Original Message- > > From: Adrian Klaver > > Sent: Tuesday, February 18, 2020 4:41 PM > > To: Bellrose, Brian ; > > pgsql-general@lists.postgresql.org > > Subject: Re: [EXTERNAL]: Re: pglogical install errors openSUSE Leap > > 42.1 > > > > On 2/18/20 1:22 PM, Bellrose, Brian wrote: > >> I used zypper to install postgres. Postgres is fine... Running 9.4.25 that > >> I updated today. So you have krb5-devel... Ok, good to know. Seems like > >> that may be the missing link. At least I hope it is the only one. > > > > Was postgresql94-devel also
RE: [EXTERNAL]: Re: pglogical install errors openSUSE Leap 42.1
Thanks for the info. I will investigate these steps. Brian -Original Message- From: Giulio Calacoci Sent: Wednesday, February 19, 2020 9:07 AM To: Bellrose, Brian Cc: Adrian Klaver ; pgsql-general@lists.postgresql.org Subject: Re: [EXTERNAL]: Re: pglogical install errors openSUSE Leap 42.1 Hi, I'm not an OpenSuse expert, but I know a bit about building pglogical... I've been able to compile pglogical on a leap 42.3 docker image In the beginning, I tried to compile pglogical using the very same postgresql packages you were using, but for some reasons, I was consistently receiving linking errors. After some debugging, and packages digging, I found some official SUSE packages for leap 42 So I decided to give them a try. Here is the URL to the repo: https://urldefense.proofpoint.com/v2/url?u=https-3A__download.opensuse.org_repositories_openSUSE-3A_Leap-3A_42.3-3A_Update_ports_openSUSE-3ALeap-3A42.3-3AUpdate.repo&d=DwIFaQ&c=pG3N8eJDEvizGbIy8hw-0w&r=Avd8jJaAOeUVJ3AmVEriBLvYqiK8qqaP7C8huZnWyCI&m=YCSK4nrYEqdAY-8Bp8EfSfbaQsdFk6R8BWkTYwfFz0s&s=Y25bebtRT7XasHdr19dUMsGiUfJVFJ7cyK88MiaC1GE&e= Note: here https://urldefense.proofpoint.com/v2/url?u=https-3A__download.opensuse.org_repositories_openSUSE-3A_Leap-3A_&d=DwIFaQ&c=pG3N8eJDEvizGbIy8hw-0w&r=Avd8jJaAOeUVJ3AmVEriBLvYqiK8qqaP7C8huZnWyCI&m=YCSK4nrYEqdAY-8Bp8EfSfbaQsdFk6R8BWkTYwfFz0s&s=kKXgcuSUbzsujz8MMh0GcsLa5Yy7-Wsj8iI3wH2DD7E&e= you can find also repositories for older leap versions. including 42.1 An important suggestion I can give you is: please use a checkout from git instead of using a tar file containing the source code. This because pglogical uses a git submodule for the pglogical_dump component. Here is the link to the 2ndquadrant source repository: https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_2ndQuadrant_pglogical&d=DwIFaQ&c=pG3N8eJDEvizGbIy8hw-0w&r=Avd8jJaAOeUVJ3AmVEriBLvYqiK8qqaP7C8huZnWyCI&m=YCSK4nrYEqdAY-8Bp8EfSfbaQsdFk6R8BWkTYwfFz0s&s=f6n6qhkn5ychzPwu3NM6-Y7gPkZ0XkHjhmWKg4_MxNI&e= Here is the sequence of action I performed: zypper addrepo https://urldefense.proofpoint.com/v2/url?u=https-3A__download.opensuse.org_repositories_openSUSE-3A_Leap-3A_42.3-3A_Update_ports_openSUSE-3ALeap-3A42.3-3AUpdate.repo&d=DwIFaQ&c=pG3N8eJDEvizGbIy8hw-0w&r=Avd8jJaAOeUVJ3AmVEriBLvYqiK8qqaP7C8huZnWyCI&m=YCSK4nrYEqdAY-8Bp8EfSfbaQsdFk6R8BWkTYwfFz0s&s=Y25bebtRT7XasHdr19dUMsGiUfJVFJ7cyK88MiaC1GE&e= zypper install postgresql94-devel openssl-devel libxml2-devel libxslt-devel pam-devel readline-devel zlib-devel krb5-devel e2fsprogs-devel libselinux-devel git vim then I cloned the pglogical git: git clone https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_2ndQuadrant_pglogical.git&d=DwIFaQ&c=pG3N8eJDEvizGbIy8hw-0w&r=Avd8jJaAOeUVJ3AmVEriBLvYqiK8qqaP7C8huZnWyCI&m=YCSK4nrYEqdAY-8Bp8EfSfbaQsdFk6R8BWkTYwfFz0s&s=dGJuVm7AZqax8w_zo12SgV2Sidcm6TmsftvP151tv2I&e= (note, I used the https link because in docker I was having some issues using the ssh protocol. if you face the same issue update the .gitmodule file inside the repository checkout to use the https link to pglogical_dump https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_2ndQuadrant_pglogical-5Fdump.git&d=DwIFaQ&c=pG3N8eJDEvizGbIy8hw-0w&r=Avd8jJaAOeUVJ3AmVEriBLvYqiK8qqaP7C8huZnWyCI&m=YCSK4nrYEqdAY-8Bp8EfSfbaQsdFk6R8BWkTYwfFz0s&s=5d5i8InHaQ8ITofwHCD7qwHQ_wvqPvKl9kxLzap3PHA&e= ) git checkout REL2_3_0 (this because I wanted to use the latest released code. feel free to use another tag if you wish) git submodule sync git submodule init git submodule update now the repository is up to date. PATH=/usr/lib/postgresql94/bin:$PATH make USE_PGXS=1 clean all PATH=/usr/lib/postgresql94/bin:$PATH make USE_PGXS=1 install (as you can notice the path is different from the documentation as suse uses different default paths from rhel and debian) This worked for me on OpenSUSE leap 42.3, PLEASE let me know if it works for you too. Ciao, Giulio On Tue, 18 Feb 2020 at 23:07, Bellrose, Brian wrote: > > I found a repository that offered it and added the repo. > https://urldefense.proofpoint.com/v2/url?u=https-3A__download.opensuse > .org_repositories_home-3Avjt-3Aifad_openSUSE-5FLeap-5F42.1_home-3Avjt- > 3Aifad.repo&d=DwIFaQ&c=pG3N8eJDEvizGbIy8hw-0w&r=Avd8jJaAOeUVJ3AmVEriBL > vYqiK8qqaP7C8huZnWyCI&m=YCSK4nrYEqdAY-8Bp8EfSfbaQsdFk6R8BWkTYwfFz0s&s= > nvwV34wE61EoyLrFp7Y9w1OHj_UHjOVDahM6y3lveT4&e= > > Only time will tell if that was a wise decision. I only need to it to get me > through my upgrade. Moving all these to RHEL 8.1 and Postgres 11.7. Going to > use pglogical to try and reduce downtime. > > Brian > > -Original Message- > From: Adrian Klaver > Sent: Tuesday, February 18, 2020 5:00 PM > To: Bellrose, Brian ; > pgsql-general@lists.postgresql.org > Subject: Re: [EXTERNAL]: Re: pglogical install errors openSUSE Leap > 42.1 > > On 2/18/20 1:56 PM, Bellrose, Brian wrote: > > Yes, > > > > S | Name | Summary
