4 ms·
> Parallel execution of sequential scans, joins and aggregates This confuses me—I thought a sequential scan was slow because it was limited by disk I/O; why is
by thristian 10y ago
> Parallel execution of sequential scans, joins and aggregates
This confuses me—I thought a sequential scan was slow because it was limited by disk I/O; why is it faster to hawe multiple threads waiting around for the same disk?
(I guessed maybe that it was running different sequential scans in the same query in parallel, but no, pages 3 and 4 of the PDF show parallel workers being used for a single scan of a single table)
- _pmf_ 10y ago> why is it faster to have multiple threads waiting around for the same disk? RAID?
- dunkelheit 10y ago> I thought a sequential scan was slow because it was limited by disk I/O Not necessarily. Suppose you have a fairly big table that fits in the shared buffer cache and you run a query with a predicate that returns only a handful of rows. Then you would be better off scanning parts of it in parallel and then combining the results.
- katnegermis 10y agoI was wondering the exact same thing -- how can CPU-parallelism improve on a task which (to me at least) seems to be disk-IO bound? I'm guessing that my assumption of the task being disk-IO bound is incorrect!
- jaziek 10y agoIt's only disk IO bound if you're accessing the disk. With modern servers packing multiple TB of memory, it is likely you won't be accessing the disk at all.
- mianos 10y agoIt is worth checking out the wiki on the details of the parallel query for how and when it works: https://wiki.postgresql.org/wiki/Parallel_Query https://wiki.postgresql.org/wiki/Parallel_Query
- amitlan 10y agoor read this chapter (the wiki page's content made into the official documentation): https://www.postgresql.org/docs/devel/static/parallel-query.html https://www.postgresql.org/docs/devel/static/parallel-query....
- macdice 10y agoParallel sequential scan is a building block that enables other work to be done in parallel. Once you've got a way to split up a sequential scan by handing chunks to different workers, those workers can then do other CPU intensive work like filtering (involving potentially expensive expressions), partial aggregation (for example summing a subtotal that will be added to other workers' subtotals at the end) and joining (say against a hash table).
- gaius 10y agoBecause "the disk" is the wrong abstraction here; think instead of stuffing I/O requests into a queue, letting the storage controller re-order them, then giving you a callback when the blocks are ready.
- postila 10y agoFor some cases (like single HDD and low amount of RAM) it should be so, but for some cases(from "the whole table is already loaded to RAM" to "SSD" and to "a table is partitioned among multiple drives"), it must give very good improvements for analytical queries.