pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCREATE INDEX as bottleneck
4+ messages / 3 participants
[nested] [flat]
* CREATE INDEX as bottleneck
@ 2010-11-11 13:41 Marc Mamin <M.Mamin@intershop.de>
2010-11-11 13:58 ` Re: CREATE INDEX as bottleneck Kenneth Marshall <ktm@rice.edu>
2010-11-11 18:54 ` Re: CREATE INDEX as bottleneck Alex Hunsaker <badalex@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Marc Mamin @ 2010-11-11 13:41 UTC (permalink / raw)
To: pgsql-performance
Hello,
in the last years, we have successfully manage to cope with our data
growth
using partitioning and splitting large aggregation tasks on multiple
threads.
The partitioning is done logically by our applicationn server, thus
avoiding trigger overhead.
There are a few places in our data flow where we have to wait for index
creation before being able to distribute the process on multiple threads
again.
With the expected growth, create index will probably become a severe
bottleneck for us.
Is there any chance to see major improvement on it in a middle future ?
I guess the question is naive, but why can't posgres use multiple
threads for large sort operation ?
best regards,
Marc Mamin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CREATE INDEX as bottleneck
2010-11-11 13:41 CREATE INDEX as bottleneck Marc Mamin <M.Mamin@intershop.de>
@ 2010-11-11 13:58 ` Kenneth Marshall <ktm@rice.edu>
1 sibling, 0 replies; 4+ messages in thread
From: Kenneth Marshall @ 2010-11-11 13:58 UTC (permalink / raw)
To: Marc Mamin <M.Mamin@intershop.de>; +Cc: pgsql-performance
On Thu, Nov 11, 2010 at 02:41:12PM +0100, Marc Mamin wrote:
> Hello,
>
> in the last years, we have successfully manage to cope with our data
> growth
> using partitioning and splitting large aggregation tasks on multiple
> threads.
> The partitioning is done logically by our applicationn server, thus
> avoiding trigger overhead.
>
> There are a few places in our data flow where we have to wait for index
> creation before being able to distribute the process on multiple threads
> again.
>
> With the expected growth, create index will probably become a severe
> bottleneck for us.
>
> Is there any chance to see major improvement on it in a middle future ?
> I guess the question is naive, but why can't posgres use multiple
> threads for large sort operation ?
>
>
> best regards,
>
> Marc Mamin
>
There has been a recent discussion on the hackers mailing list on
using the infrastructure that is already in place to lauch autovacuum
processes to launch other helper processes. Something like this could
be used to offload the sort process to a much more parallelize version
that could take advantage of multiple I/O streams and CPU cores. Many
things are possible given the appropriate resources: funding, coding
and development cycles...
Regards,
Ken
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CREATE INDEX as bottleneck
2010-11-11 13:41 CREATE INDEX as bottleneck Marc Mamin <M.Mamin@intershop.de>
@ 2010-11-11 18:54 ` Alex Hunsaker <badalex@gmail.com>
2010-11-11 20:05 ` Re: CREATE INDEX as bottleneck Marc Mamin <M.Mamin@intershop.de>
1 sibling, 1 reply; 4+ messages in thread
From: Alex Hunsaker @ 2010-11-11 18:54 UTC (permalink / raw)
To: Marc Mamin <M.Mamin@intershop.de>; +Cc: pgsql-performance
On Thu, Nov 11, 2010 at 06:41, Marc Mamin <M.Mamin@intershop.de> wrote:
> There are a few places in our data flow where we have to wait for index
> creation before being able to distribute the process on multiple threads
> again.
Would CREATE INDEX CONCURRENTLY help here?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CREATE INDEX as bottleneck
2010-11-11 13:41 CREATE INDEX as bottleneck Marc Mamin <M.Mamin@intershop.de>
2010-11-11 18:54 ` Re: CREATE INDEX as bottleneck Alex Hunsaker <badalex@gmail.com>
@ 2010-11-11 20:05 ` Marc Mamin <M.Mamin@intershop.de>
0 siblings, 0 replies; 4+ messages in thread
From: Marc Mamin @ 2010-11-11 20:05 UTC (permalink / raw)
To: Alex Hunsaker <badalex@gmail.com>; +Cc: pgsql-performance
No, CONCURRENTLY is to improve table availability during index creation, but it degrades the performances.
best regards,
Marc Mamin
-----Original Message-----
From: Alex Hunsaker [mailto:badalex@gmail.com]
Sent: Donnerstag, 11. November 2010 19:55
To: Marc Mamin
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] CREATE INDEX as bottleneck
On Thu, Nov 11, 2010 at 06:41, Marc Mamin <M.Mamin@intershop.de> wrote:
> There are a few places in our data flow where we have to wait for index
> creation before being able to distribute the process on multiple threads
> again.
Would CREATE INDEX CONCURRENTLY help here?
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-11-11 20:05 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-11 13:41 CREATE INDEX as bottleneck Marc Mamin <M.Mamin@intershop.de>
2010-11-11 13:58 ` Kenneth Marshall <ktm@rice.edu>
2010-11-11 18:54 ` Alex Hunsaker <badalex@gmail.com>
2010-11-11 20:05 ` Marc Mamin <M.Mamin@intershop.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox