Unable to test parallel aggregate/joins in Postgres beta 2

Oops, the hyper links got removed.

RPM repo - http://yum.postgresql.org/repopackages.php#pg96

Blog article followed for parallel aggregation testing -
http://blog.2ndquadrant.com/parallel-aggregate/

On Thu, Jul 14, 2016 at 6:46 PM, Madusudanan.B.N <b.n.madusudanan@gmail.com>
wrote:

Hi,

I installed postgres beta 2 using the RPM provided here for Cent OS.

Based on this article I was trying to set *max_parallel_degree*, but
postgres complained that there was no such config variable. But I was able
to set *max_parallel_workers_per_gather* and test things for parallel
sequential scan

I have a hunch that I have installed a previous beta version i.e beta 1,
but *Select version()* reported that I was using 9.6beta2 only.

What have I done wrong ?

1) The RPM file that I downloaded has 9.6 beta 1. Name is mentioned as
pgdg-redhat96-9.6-1.noarch.rpm.

2) Should beta2 built only from source ? If the RPM is beta 1 why is the
server version reporting as beta2 ?

3) Is there any kind of toggle to enable parallel aggregate/join feature ?

--
Regards,
Madusudanan.B.N <http://madusudanan.com&gt;

--
Regards,
Madusudanan.B.N <http://madusudanan.com&gt;

Hi,

On Thu, 2016-07-14 at 18:46 +0530, Madusudanan.B.N wrote:

I installed postgres beta 2 using the RPM provided here for Cent OS.

Based on this article I was trying to set *max_parallel_degree*, but
postgres complained that there was no such config variable. But I was able
to set *max_parallel_workers_per_gather* and test things for parallel
sequential scan

That article seems to be pre-beta2. The name of the parameter was changed to
max_parallel_workers_per_gather, so what you did was correct.

I have a hunch that I have installed a previous beta version i.e beta 1,
but *Select version()* reported that I was using 9.6beta2 only.

What makes you feel like you have beta1 installed? Can I get the output of:

rpm -qa|grep postgresql

What have I done wrong ?

1) The RPM file that I downloaded has 9.6 beta 1. Name is mentioned as
pgdg-redhat96-9.6-1.noarch.rpm.

This is *not* PostgreSQL version -- this is the repository RPM only.

2) Should beta2 built only from source ? If the RPM is beta 1 why is the
server version reporting as beta2 ?

See above.

Regards,

--
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR

On Thu, Jul 14, 2016 at 11:16 PM, Madusudanan.B.N
<b.n.madusudanan@gmail.com> wrote:

3) Is there any kind of toggle to enable parallel aggregate/join feature ?

I am able to generate parallel plan, The parallel plan may be costly
in your query compared
to other scans, because of which it is not selecting the parallel plan.

It is possible that if the table size is very small or you are
selecting all records of the table
and etc.

postgres=# insert into test values(generate_series(1,1000000), 'Test');
INSERT 0 1000000
postgres=# explain select * from test where f1 < 9900;
QUERY PLAN
-----------------------------------------------------------------------------
Gather (cost=1000.00..23719.93 rows=188964 width=105)
Workers Planned: 2
-> Parallel Seq Scan on test (cost=0.00..22719.93 rows=78735 width=105)
Filter: (f1 < 9900)
(4 rows)

Regards,
Hari Babu
Fujitsu Australia

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

++ Pg hackers

On Thu, Jul 14, 2016 at 7:18 PM, Madusudanan.B.N <b.n.madusudanan@gmail.com>
wrote:

On Thu, Jul 14, 2016 at 6:59 PM, Devrim Gündüz <devrim@gunduz.org> wrote:

Hi,

On Thu, 2016-07-14 at 18:46 +0530, Madusudanan.B.N wrote:

I installed postgres beta 2 using the RPM provided here for Cent OS.

Based on this article I was trying to set *max_parallel_degree*, but
postgres complained that there was no such config variable. But I was

able

to set *max_parallel_workers_per_gather* and test things for parallel
sequential scan

That article seems to be pre-beta2. The name of the parameter was changed
to
max_parallel_workers_per_gather, so what you did was correct.

I have a hunch that I have installed a previous beta version i.e beta 1,
but *Select version()* reported that I was using 9.6beta2 only.

What makes you feel like you have beta1 installed? Can I get the output
of:

rpm -qa|grep postgresql

My bad, the installation is correct, it reports the following version
numbers as expected.

postgresql96-contrib-9.6beta2-2PGDG.rhel6.x86_64
postgresql96-9.6beta2-2PGDG.rhel6.x86_64
postgresql96-libs-9.6beta2-2PGDG.rhel6.x86_64
postgresql96-server-9.6beta2-2PGDG.rhel6.x86_64

I though that since parallel sequential scan was committed earlier and in
my testing parallelism was working correctly, perhaps I was testing in an
earlier version.

But the reason why it did not chose a parallel plan was because of an
order by clause without an index. Does pg support parallel sort ? This was
in the dev pipeline but not sure whether the work is done already.

Again, I am not completely sure that an order by clause is the reason, ill
test more and get back.

What have I done wrong ?

1) The RPM file that I downloaded has 9.6 beta 1. Name is mentioned as
pgdg-redhat96-9.6-1.noarch.rpm.

This is *not* PostgreSQL version -- this is the repository RPM only.

Thanks for that information. I'll keep that mind.

2) Should beta2 built only from source ? If the RPM is beta 1 why is the
server version reporting as beta2 ?

See above.

Regards,

--
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR

--
Regards,
Madusudanan.B.N <http://madusudanan.com&gt;

--
Regards,
Madusudanan.B.N <http://madusudanan.com&gt;

On Thu, Jul 14, 2016 at 7:09 PM, Haribabu Kommi <kommi.haribabu@gmail.com>
wrote:

On Thu, Jul 14, 2016 at 11:16 PM, Madusudanan.B.N
<b.n.madusudanan@gmail.com> wrote:

3) Is there any kind of toggle to enable parallel aggregate/join feature

?

I am able to generate parallel plan, The parallel plan may be costly
in your query compared
to other scans, because of which it is not selecting the parallel plan.

It is possible that if the table size is very small or you are
selecting all records of the table
and etc.

postgres=# insert into test values(generate_series(1,1000000), 'Test');
INSERT 0 1000000
postgres=# explain select * from test where f1 < 9900;
QUERY PLAN

-----------------------------------------------------------------------------
Gather (cost=1000.00..23719.93 rows=188964 width=105)
Workers Planned: 2
-> Parallel Seq Scan on test (cost=0.00..22719.93 rows=78735
width=105)
Filter: (f1 < 9900)
(4 rows)

For the above example, I can see that it does choose parallel plan.
However as said above, for other cases it does not choose a parallel plan.

Is there any other considerations apart from the mentioned ones on why pg
would not choose a parallel plan ?

Regards,
Hari Babu
Fujitsu Australia

--
Regards,
Madusudanan.B.N <http://madusudanan.com&gt;

On Thu, Jul 14, 2016 at 7:22 PM, Madusudanan.B.N
<b.n.madusudanan@gmail.com> wrote:

On Thu, Jul 14, 2016 at 7:09 PM, Haribabu Kommi <kommi.haribabu@gmail.com>
wrote:

On Thu, Jul 14, 2016 at 11:16 PM, Madusudanan.B.N
<b.n.madusudanan@gmail.com> wrote:

3) Is there any kind of toggle to enable parallel aggregate/join feature
?

I am able to generate parallel plan, The parallel plan may be costly
in your query compared
to other scans, because of which it is not selecting the parallel plan.

It is possible that if the table size is very small or you are
selecting all records of the table
and etc.

postgres=# insert into test values(generate_series(1,1000000), 'Test');
INSERT 0 1000000
postgres=# explain select * from test where f1 < 9900;
QUERY PLAN

-----------------------------------------------------------------------------
Gather (cost=1000.00..23719.93 rows=188964 width=105)
Workers Planned: 2
-> Parallel Seq Scan on test (cost=0.00..22719.93 rows=78735
width=105)
Filter: (f1 < 9900)
(4 rows)

For the above example, I can see that it does choose parallel plan. However
as said above, for other cases it does not choose a parallel plan.

Is there any other considerations apart from the mentioned ones on why pg
would not choose a parallel plan ?

You can try by setting parallel_setup_cost=0 and
parallel_tuple_cost=0, though changing that way is not advisable. If
that doesn't work for you, share the exact test for which you are
expecting parallel plan to be selected.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers