PostgreSQL parallel scan with “greater” or “less” than

execution-planpostgresql

I have two queries. One has "=" other one has ">" in as filter condition. My question is why planner plans only query with equals in condition to run in parallel.

If I mess with defaults for parallel_tuple_cost and parallel_setup_cost I can make both run in parallel. But I would like to know why it is planned this way with default configuration.

I am providing queries which I executed and planner explain output. There are no indexes in for fields which are used in fiter.

db scheme

This runs in parallel with two workers:

VACUUM; EXPLAIN ANALYSE select screen_name from accounts where followers_count = 100;

(cost=1000.00..81857.06 rows=401 width=12) (actual time=0.442..149.981 rows=5028 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on accounts  (cost=0.00..80816.96 rows=167 width=12) (actual time=0.100..113.974 rows=1676 loops=3)
        Filter: (followers_count = 100)
        Rows Removed by Filter: 1154464
Planning Time: 0.040 ms
Execution Time: 150.294 ms

This runs with single worker:

VACUUM; EXPLAIN ANALYSE select screen_name from accounts where followers_count > 100;

Seq Scan on accounts  (cost=0.00..106105.64 rows=2278992 width=12) (actual time=2.608..562.870 rows=2286022 loops=1)
  Filter: (followers_count > 100)
  Rows Removed by Filter: 1182399
Planning Time: 0.059 ms
JIT:
  Functions: 4
   Options: Inlining false, Optimization false, Expressions true, Deforming true"
   Timing: Generation 0.369 ms, Inlining 0.000 ms, Optimization 0.186 ms, Emission 2.316 ms, Total 2.871 ms"
Execution Time: 640.952 ms

Queries have only different sign in filter condition.

EDIT1:

Using less conservative settings:

SET parallel_tuple_cost to 0;
SET parallel_setup_cost to 0;
SET max_parallel_workers_per_gather to 4;
VACUUM; EXPLAIN ANALYSE select * from accounts where followers_count > 100;
    
Gather  (cost=0.00..73589.74 rows=2278693 width=121) (actual time=0.220..391.700 rows=2286022 loops=1)
  Workers Planned: 4
  Workers Launched: 4
  ->  Parallel Seq Scan on accounts  (cost=0.00..73589.74 rows=569673 width=121) (actual time=0.024..117.873 rows=457204 loops=5)
        Filter: (followers_count > 100)
        Rows Removed by Filter: 236480
Planning Time: 0.065 ms
Execution Time: 484.223 ms

Using default settings:

SET parallel_tuple_cost to 0.1;
SET parallel_setup_cost to 1000;
SET max_parallel_workers_per_gather to 2;
VACUUM; EXPLAIN ANALYSE select * from accounts where followers_count > 100;

Seq Scan on accounts  (cost=0.00..106100.83 rows=2278739 width=121) (actual time=2.610..552.259 rows=2286022 loops=1)
  Filter: (followers_count > 100)
  Rows Removed by Filter: 1182399
Planning Time: 0.083 ms
JIT:
  Functions: 2
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 0.462 ms, Inlining 0.000 ms, Optimization 0.175 ms, Emission 2.239 ms, Total 2.876 ms"
Execution Time: 634.788 ms

Best Answer

Your > query returns hugely more rows than =. Each of the rows being returned from a parallel worker needs to get passed up from the parallel worker to the leader, and doing this is not free. (This is the cost accounted for by parallel_tuple_cost). A row filtered out by the parallel worker, on the other hand, does not suffer this expense as it is not passed up. If most rows will be filtered out, it makes sense to do the filtering in parallel. If few rows will be filtered out, it might not make sense to do the filtering in parallel but then do the extra work of shuffling the data around between processes.

What you have not shown us is whether doing the > query in parallel is actually faster than doing it in a single process. Maybe you need to tune parallel_tuple_cost down some. The default settings is quite conservative.

Related Question