On 11/08/2011 09:28 AM, Ondrej Ivanič wrote:
> I have simple question (I think which is not easy to answer): why
> Postgres is so slow comparing to other Postgres based MPP products
Which one(s) are you referring to? In what kind of workloads?
Are you talking about Greenplum or similar?
> Another interesting experiment was to parallelise query by hand:
> select sum(count) from T where org_id = ... and date_in_tz>=
> '2011-08-01' and date_in_tz< '2011-11-01'
Pg isn't very good at parallelism within a single query. It handles lots
of small queries concurrently fairly well, but isn't as good at using
all the resources of a box on one big query because it can only use one
CPU per query and has only a very limited ability to do concurrent I/O
on a single query too.
That said, you should be tuning effective_io_concurrency to match your
storage; if you're not, then you aren't getting the benefit of the
concurrent I/O that PostgreSQL *is* capable of. You'll also need to have
tweaked your shared_buffers, work_mem etc appropriately for your query
workload.
Since Pg needs some kind of pooling and admission control to perform
very well in OLTP, it's only highly performant without addons in the
middle of the range - medium numbers of medium-complexity queries. For
huge numbers of simple queries it needs a pooler, and for small numbers
of hugely complex queries it won't perform all that well without
something to try to parallelise the queries outside Pg.
> All above were faster than single query at the begging.
I'm not at all surprised by that. PostgreSQL couldn't use the full
resources of your system when it was expressed as just one query.
--
Craig Ringer