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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
