From: | Marc Mamin <M(dot)Mamin(at)intershop(dot)de> |
---|---|
To: | "'Klaus P(dot)'" <kpi6288(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Do parallel queries work with only dblink not with fdw? |
Date: | 2016-05-04 14:10:08 |
Message-ID: | B6F6FD62F2624C4C9916AC0175D56D8842135373@jenmbs01.ad.intershop.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Klaus P.
>Sent: Mittwoch, 4. Mai 2016 10:28
>To: pgsql-general(at)postgresql(dot)org
>Subject: [GENERAL] Do parallel queries work with only dblink not with fdw?
>
>In order to improve cpu and disk utilization, I am testing parallel queries.
>
>The approach with dblink_send_query() and dblink_get_result() works in my proof-of-concept. Runtime of my reference query was reduced from 55 seconds to ~20seconds using 4 parallel connections. Not what I had hoped but certainly a significant improvement.
>My approach is fairly easy:
>db_link_send_query('conn1', 'statement based on partitioning field');
>db_link_send_query('conn2', 'statement based on partitioning field');
>
>...
>SELECT
>dblink_get_result('conn1')
>UNION ALL
>dblink_get_result('conn2')
>
>...
Hello,
I'm using the db_link approach too, but rather than using dblink_get_result, I store the result in an additional table.
This was faster, at least for my use case and at the time when I implemented that solution...
something like
db_link_send_query('conn0', 'CREATE UNLOGGED TABLE my_result...
db_link_send_query('conn1', 'INSERT INTO my_result statement based on partitioning field');
db_link_send_query('conn2', 'INSERT INTO my_result statement based on partitioning field');
...
select * from my_result
regards,
Marc Mamin
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2016-05-04 14:13:04 | Re: ruby pg connection fails on centos - okay on debian and dev machine |
Previous Message | kpi6288 | 2016-05-04 14:09:52 | Re: Do parallel queries work with only dblink not with fdw? |