
PostgreSQL Parallel Query v2 - Tostino
http://rhaas.blogspot.com/2017/03/parallel-query-v2.html
======
malisper
Besides parallel queries (specifically parallel index scans), the Postgres 10
feature I'm most looking forward to is the work Andres Freund has done on
using LLVM to JIT compile expressions[0]. Based on the the layout of the
database I work on[1], an insane amount of CPU goes to processing of partial
index predicates. If the improvements are anywhere near what is being
described in the mailing list, we're going to start saving a ton of money on
CPU.

Parallel index scans will be an absolutely massive win for us. Currently
Postgres does not perform any sort of prefetching. When performing an index
scan, this results in serialized random I/O. In our case, queries wind up
using ~1/10th the I/O they could be using if there were to do more I/O in
parallel. We are expecting a dramatic speedup just from parallel index scans.
This is in addition to the bugfix in 9.6 (we're currently on 9.5) which added
proper support for index only scans over partial indexes. That bugfix should
also result in a pretty large speedup for a lot of our queries.

As soon as Postgres 10 comes out, we are immediately going to upgrade. There
are just so many huge wins for us.

[0] [https://www.postgresql.org/message-
id/20161206034955.bh33pae...](https://www.postgresql.org/message-
id/20161206034955.bh33paeralxbtluv%40alap3.anarazel.de)

[1] [https://blog.heapanalytics.com/running-10-million-
postgresql...](https://blog.heapanalytics.com/running-10-million-postgresql-
indexes-in-production/)

~~~
fnord123
It should be interesting, but LLVM jit brings in a substantial up front cost.
Maybe Andres found a way to mitigate it.

~~~
jordanthoms
Is it significant when you are talking about​ analytical queries which are
running for minutes or hours? Seems like it wouldn't be a huge amount of code
to JIT compared to the payoff?

~~~
electrum
Presto, which is designed for analytics, does JIT to bytecode on every query
and still works fine on sub-second queries. One potential difference with LLVM
is that the JVM will profile and only generate machine code for hot code, so
short queries might use JVM interpreted bytecode.

But you are absolutely correct that for long running analytic queries, the up
front cost of generating optimized code pays off.

------
fleetside72
The relationship between EnterpriseDB and the at-large Postgres community has
always intrigued me. Why do they turn over to the community what they could
make part of their proprietary fork? Maybe they make a lot of money consulting
and not just licenses and general at-large adoption is good for that side of
business.

~~~
mulmen
I have never used EnterpriseDB and I am only aware of it from a distance but
it has always struck me as the ideal open source arrangement. Sure,
EnterpriseDB is never going to have the revenue numbers of Oracle but their
management and business teams should have a much easier time sleeping at
night. I think it is great that EnterpriseDB has identified that it is better
for their company to exist as part of a healthy ecosystem instead of trying to
pave paradise so to speak.

~~~
b34r
A healthy relationship with open source projects is a great way to attract
talent and gain goodwill.

------
Tostino
That is really an amazing amount of work that has gone into this next release.

This is really getting Postgres up to par with the commercial offerings, and
hopefully it keeps progressing for v11.

It's especially important now since servers with more and more cores are
becoming the norm.

------
anarazel
Robert and his team are really doing an impressive amount of progress and work
on this.

~~~
pgaddict
Definitely agreed.

