Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Any way to use >1 Core in PostgreSQL for a single Connection/Query?

I get that Postgres scales automatically to multicore with multiple connections, but what about when I'm running a massive query on a SINGLE connection? So frustrating that the CPU usage maxes out at 25% on my 4-core system.

I'm in process of switching from SQL Server and this is the only thing so far that really bugs me. SQL Server will use up to 100% of my CPU for a single connection/query.

I'm running 9.2 on Windows 7 Enterprise 64-bit with Xeon processor if it matters.

If there is not way to get around this, could someone address why this isn't seen as an issue? Is it because Postgres is focused on multi-user scenarios?

like image 929
ScottEdwards2000 Avatar asked Aug 16 '13 07:08

ScottEdwards2000


People also ask

Does PostgreSQL use multiple cores?

PostgreSQL uses only one core.

Is PostgreSQL single threaded?

The PostgreSQL server is process-based (not threaded). Each database session connects to a single PostgreSQL operating system (OS) process. Multiple sessions are automatically spread across all available CPUs by the OS. The OS also uses CPUs to handle disk I/O and run other non-database tasks.

Does Postgres support multithreading?

Postgres currently supports full parallelism in client-side code. Applications can open multiple database connections and manage them asyncronously, or via threads.

What is parallel query in PostgreSQL?

Parallel queries in PostgreSQL have the ability to use more than one CPU core per query. In parallel queries the optimizer breaks down the query tasks into smaller parts and spreads each task across multiple CPU cores.


2 Answers

PostgreSQL does not currently support executing a single query across multiple CPU cores (minus background things like background writing and wal writing if you're doing a write query, but that doesn't really count). It's work that's in progress, but it's a long-term project, and is not in any current version of PostgreSQL.

This is the same on all platforms and architectures.

It is definitely an issue, but since PostgreSQL is, as you say, focused on multi user scenarios, it's not bubbled to the top of the priority queue until recently. But there are definitely people realizing it's an issue, and working on solving it for future versions, it's just not done yet.

like image 152
Magnus Hagander Avatar answered Oct 24 '22 09:10

Magnus Hagander


There is a Foreign Data Wrapper that aims to add parallelism via the GPU called pg_strom. I've never used it, and it looks quite specialized, but maybe you (or someone here) has a use-case for it.

Article describing pg_strom

http://gpuscience.com/software/postgresql-gpu-pgstrom/

The code:

https://github.com/kaigai/pg_strom

like image 20
bma Avatar answered Oct 24 '22 10:10

bma