Horton

Thread

  1. parallel-processing multiple similar query tasks - any example?

    Shaozhong SHI <shishaozhong@gmail.com> — 2022-04-27T23:33:49Z

    multiple similar query tasks are as follows:

select * from a_table where country ='UK'
select * from a_table where country='France'
and so on

How best to parallel-processing such types of multiple similar query tasks?

Any example available?

Regards,

David

  2. parallel-processing multiple similar query tasks - any example?

    Shaozhong SHI <shishaozhong@gmail.com> — 2022-04-27T23:34:38Z

    multiple similar query tasks are as follows:

select * from a_table where country ='UK'
select * from a_table where country='France'
and so on

How best to parallel-processing such types of multiple similar query tasks?

Any example available?

Regards,

David

  3. Re: parallel-processing multiple similar query tasks - any example?

    David G. Johnston <david.g.johnston@gmail.com> — 2022-04-27T23:44:14Z

    On Wed, Apr 27, 2022 at 4:34 PM Shaozhong SHI <shishaozhong@gmail.com>
wrote:

> multiple similar query tasks are as follows:
>
> select * from a_table where country ='UK'
> select * from a_table where country='France'
> and so on
>
> How best to parallel-processing such types of multiple similar query tasks?
>
> Any example available?
>
>
You should search for how to run processes/commands in parallel in whatever
client-side execution environment you want to use.  There isn't anything
specific to PostgreSQL here.

David J.

  4. Re: parallel-processing multiple similar query tasks - any example?

    David G. Johnston <david.g.johnston@gmail.com> — 2022-04-28T01:18:23Z

    On Wed, Apr 27, 2022 at 4:44 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

> On Wed, Apr 27, 2022 at 4:34 PM Shaozhong SHI <shishaozhong@gmail.com>
> wrote:
>
>> multiple similar query tasks are as follows:
>>
>> select * from a_table where country ='UK'
>> select * from a_table where country='France'
>> and so on
>>
>> How best to parallel-processing such types of multiple similar query
>> tasks?
>>
>> Any example available?
>>
>>
> You should search for how to run processes/commands in parallel in
> whatever client-side execution environment you want to use.  There isn't
> anything specific to PostgreSQL here.
>
>
You should also read:

https://www.postgresql.org/docs/current/parallel-query.html

If you want to see when a single query can be executed using parallel
workers.

David J.

  5. Re: parallel-processing multiple similar query tasks - any example?

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2022-04-28T15:17:13Z

    On 2022-Apr-28, Shaozhong SHI wrote:

> multiple similar query tasks are as follows:
> 
> select * from a_table where country ='UK'
> select * from a_table where country='France'
> and so on
> 
> How best to parallel-processing such types of multiple similar query tasks?
> 
> Any example available?

for country in UK France Germany Ireland; do
  echo "select pg_sleep(1); select * from a_table where country = '${country//\'/''/}'"
done | \
  xargs -d"\n" -P10 -n1 psql -X -c

Note the ${country/} stuff is a bash-ism.

-- 
Álvaro Herrera

  6. Re: parallel-processing multiple similar query tasks - any example?

    Shaozhong SHI <shishaozhong@gmail.com> — 2022-04-28T15:54:30Z

    No, No.

Why sleep(1)?

It should be all active - doing work concurrently.

Regards,

David

On Thu, 28 Apr 2022 at 16:17, Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:

> On 2022-Apr-28, Shaozhong SHI wrote:
>
> > multiple similar query tasks are as follows:
> >
> > select * from a_table where country ='UK'
> > select * from a_table where country='France'
> > and so on
> >
> > How best to parallel-processing such types of multiple similar query
> tasks?
> >
> > Any example available?
>
> for country in UK France Germany Ireland; do
>   echo "select pg_sleep(1); select * from a_table where country =
> '${country//\'/''/}'"
> done | \
>   xargs -d"\n" -P10 -n1 psql -X -c
>
> Note the ${country/} stuff is a bash-ism.
>
> --
> Álvaro Herrera
>

  7. Re: parallel-processing multiple similar query tasks - any example?

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2022-04-28T16:10:00Z

    On 2022-Apr-28, Shaozhong SHI wrote:

> Why sleep(1)?

It is sleeping to show that they are running concurrently.  If it runs
five sleeps of one second each and the whole command lasts one second,
then all sleeps ran in parallel.  Had the whole command taken five
seconds, you would know that the queries ran serially.

> It should be all active - doing work concurrently.

They are all active simultaneously.  You just need to supply your own
query, without any sleeps.

-- 
Álvaro Herrera

  8. Re: parallel-processing multiple similar query tasks - any example?

    Shaozhong SHI <shishaozhong@gmail.com> — 2022-04-28T16:17:04Z

    Expand and explain please.
Regards,
David

On Thursday, 28 April 2022, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:

> On 2022-Apr-28, Shaozhong SHI wrote:
>
> > Why sleep(1)?
>
> It is sleeping to show that they are running concurrently.  If it runs
> five sleeps of one second each and the whole command lasts one second,
> then all sleeps ran in parallel.  Had the whole command taken five
> seconds, you would know that the queries ran serially.
>
> > It should be all active - doing work concurrently.
>
> They are all active simultaneously.  You just need to supply your own
> query, without any sleeps.
>
> --
> Álvaro Herrera
>

  9. Re: parallel-processing multiple similar query tasks - any example?

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2022-04-28T16:20:38Z

    On 2022-Apr-28, Shaozhong SHI wrote:

> Expand and explain please.

No, thanks.

-- 
Álvaro Herrera

  10. Re: parallel-processing multiple similar query tasks - any example?

    Shaozhong SHI <shishaozhong@gmail.com> — 2022-04-28T16:26:10Z

    Well, I guess that does not work.
Never mind.

Regards,
David


On Thursday, 28 April 2022, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:

> On 2022-Apr-28, Shaozhong SHI wrote:
>
> > Expand and explain please.
>
> No, thanks.
>
> --
> Álvaro Herrera
>

  11. Re: parallel-processing multiple similar query tasks - any example?

    Steve Midgley <science@misuse.org> — 2022-04-28T17:14:47Z

    On Wed, Apr 27, 2022 at 4:34 PM Shaozhong SHI <shishaozhong@gmail.com>
wrote:

>
>
>
> multiple similar query tasks are as follows:
>
> select * from a_table where country ='UK'
> select * from a_table where country='France'
> and so on
>
> How best to parallel-processing such types of multiple similar query tasks?
>
>
> This depends on how you are engaging with the queries when they return
results. Let's assume you are running them from a programming environment
with an ORM layer. In that case you can run each query in a separate thread
and connection pipe, and the queries will run async just fine. If you are
running at the command line using psql, you can just open multiple shells
and run each query from a different shell terminal.

Postgres is very good at async queries, so your challenge is really
figuring out how you will use the results of each query and setting up the
environment sending the queries to perform asynchronously.

Steve

  12. Re: parallel-processing multiple similar query tasks - any example?

    Shaozhong SHI <shishaozhong@gmail.com> — 2022-04-28T17:46:01Z

    On Thu, 28 Apr 2022 at 18:15, Steve Midgley <science@misuse.org> wrote:

>
>
> On Wed, Apr 27, 2022 at 4:34 PM Shaozhong SHI <shishaozhong@gmail.com>
> wrote:
>
>>
>>
>>
>> multiple similar query tasks are as follows:
>>
>> select * from a_table where country ='UK'
>> select * from a_table where country='France'
>> and so on
>>
>> How best to parallel-processing such types of multiple similar query
>> tasks?
>>
>>
>> This depends on how you are engaging with the queries when they return
> results. Let's assume you are running them from a programming environment
> with an ORM layer. In that case you can run each query in a separate thread
> and connection pipe, and the queries will run async just fine. If you are
> running at the command line using psql, you can just open multiple shells
> and run each query from a different shell terminal.
>
> Postgres is very good at async queries, so your challenge is really
> figuring out how you will use the results of each query and setting up the
> environment sending the queries to perform asynchronously.
>
> Steve
>

Hi, Steve,

That is very useful.

All we want to do is to process a large amount of data.

I found loops of recursive queries are very time consuming and will not
finish on time.

Measures like indexing are simply not adequate to address the problem.

I am thinking of making use of Linux capability to fire off concurrent
processors.

So long as it is efficient, we can always work out how to ask it to return
results.

Regards,

David

  13. Re: parallel-processing multiple similar query tasks - any example?

    Erik Brandsberg <erik@heimdalldata.com> — 2022-04-28T18:22:25Z

    None of this discussion is really specific to postgres.

On Thu, Apr 28, 2022 at 1:46 PM Shaozhong SHI <shishaozhong@gmail.com>
wrote:

>
>
> On Thu, 28 Apr 2022 at 18:15, Steve Midgley <science@misuse.org> wrote:
>
>>
>>
>> On Wed, Apr 27, 2022 at 4:34 PM Shaozhong SHI <shishaozhong@gmail.com>
>> wrote:
>>
>>>
>>>
>>>
>>> multiple similar query tasks are as follows:
>>>
>>> select * from a_table where country ='UK'
>>> select * from a_table where country='France'
>>> and so on
>>>
>>> How best to parallel-processing such types of multiple similar query
>>> tasks?
>>>
>>>
>>> This depends on how you are engaging with the queries when they return
>> results. Let's assume you are running them from a programming environment
>> with an ORM layer. In that case you can run each query in a separate thread
>> and connection pipe, and the queries will run async just fine. If you are
>> running at the command line using psql, you can just open multiple shells
>> and run each query from a different shell terminal.
>>
>> Postgres is very good at async queries, so your challenge is really
>> figuring out how you will use the results of each query and setting up the
>> environment sending the queries to perform asynchronously.
>>
>> Steve
>>
>
> Hi, Steve,
>
> That is very useful.
>
> All we want to do is to process a large amount of data.
>
> I found loops of recursive queries are very time consuming and will not
> finish on time.
>
> Measures like indexing are simply not adequate to address the problem.
>
> I am thinking of making use of Linux capability to fire off concurrent
> processors.
>
> So long as it is efficient, we can always work out how to ask it to return
> results.
>
> Regards,
>
> David
>