By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
prepare check_async_settings as
select name, setting, short_desc
from pg_settings
where name in ( 'max_worker_processes'
,'max_parallel_workers_per_gather'
,'max_parallel_maintenance_workers'
,'max_parallel_workers'
,'parallel_leader_participation');
execute check_async_settings;
PREPARE
| name | setting | short_desc |
|---|---|---|
| max_parallel_maintenance_workers | 2 | Sets the maximum number of parallel processes per maintenance operation. |
| max_parallel_workers | 8 | Sets the maximum number of parallel workers that can be active at one time. |
| max_parallel_workers_per_gather | 4 | Sets the maximum number of parallel processes per executor node. |
| max_worker_processes | 8 | Maximum number of concurrent worker processes. |
| parallel_leader_participation | on | Controls whether Gather and Gather Merge also run subplans. |
SELECT 5
select 1;
| ?column? |
|---|
| 1 |
SELECT 1
create table test (partition_id smallint)
partition by list(partition_id);
create table test1 partition of test for values in (1);
create table test2 partition of test for values in (2);
create table test_default partition of test default;
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
insert into test
select 1
from generate_series(1,5e5);
ERROR: could not extend file "base/5/16387": No space left on device HINT: Check free disk space.
create extension if not exists dblink;
select dblink_connect('parallel_client','');
select dblink_send_query('parallel_client',
$q$ select*from test where partition_id>=1 and partition_id<2;
$q$);
select pid,leader_pid,query from pg_stat_activity;
CREATE EXTENSION
| dblink_connect |
|---|
| OK |
SELECT 1
| dblink_send_query |
|---|
| 1 |
SELECT 1
| pid | leader_pid | query |
|---|---|---|
| 786 | null | create extension if not exists dblink; select dblink_connect('parallel_client',''); select dblink_send_query('parallel_client', $q$ select*from test where partition_id>=1 and partition_id<2; $q$); select pid,leader_pid,query from pg_stat_activity; |
| 787 | null | select*from test where partition_id>=1 and partition_id<2; |
| 736 | null | |
| 737 | null | |
| 788 | 787 | select*from test where partition_id>=1 and partition_id<2; |
| 735 | null | |
| 733 | null | |
| 732 | null |
SELECT 8
explain(analyze,verbose,settings)
select*from test where partition_id>=1 and partition_id<2;
| QUERY PLAN |
|---|
| Gather (cost=1000.00..3760.31 rows=1061 width=2) (actual time=11.186..12.641 rows=0 loops=1) |
| Output: test.partition_id |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Parallel Append (cost=0.00..2654.21 rows=442 width=2) (actual time=2.308..2.308 rows=0 loops=3) |
| Worker 0: actual time=6.852..6.852 rows=0 loops=1 |
| Worker 1: actual time=0.071..0.071 rows=0 loops=1 |
| -> Parallel Seq Scan on public.test1 test_1 (cost=0.00..2618.00 rows=616 width=2) (actual time=3.460..3.460 rows=0 loops=2) |
| Output: test_1.partition_id |
| Filter: ((test_1.partition_id >= 1) AND (test_1.partition_id < 2)) |
| Worker 0: actual time=6.851..6.851 rows=0 loops=1 |
| Worker 1: actual time=0.068..0.069 rows=0 loops=1 |
| -> Parallel Seq Scan on public.test_default test_2 (cost=0.00..34.00 rows=8 width=2) (actual time=0.001..0.001 rows=0 loops=1) |
| Output: test_2.partition_id |
| Filter: ((test_2.partition_id >= 1) AND (test_2.partition_id < 2)) |
| Worker 1: actual time=0.001..0.001 rows=0 loops=1 |
| Settings: max_parallel_workers_per_gather = '4', search_path = 'public, x_tablefunc, x_pg_trgm, x_intarray, x_hstore, x_tsm_system_rows, x_unaccent, x_ltree, x_postgis' |
| Planning Time: 0.261 ms |
| Execution Time: 12.656 ms |
EXPLAIN
explain(analyze,verbose,settings)
select*from test where partition_id=1;
| QUERY PLAN |
|---|
| Seq Scan on public.test1 test (cost=0.00..3388.00 rows=1047 width=2) (actual time=1.713..1.714 rows=0 loops=1) |
| Output: test.partition_id |
| Filter: (test.partition_id = 1) |
| Settings: max_parallel_workers_per_gather = '4', search_path = 'public, x_tablefunc, x_pg_trgm, x_intarray, x_hstore, x_tsm_system_rows, x_unaccent, x_ltree, x_postgis' |
| Planning Time: 0.102 ms |
| Execution Time: 1.723 ms |
EXPLAIN
set max_parallel_workers=0;
set max_parallel_workers_per_gather=0;
execute check_async_settings;
SET
SET
| name | setting | short_desc |
|---|---|---|
| max_parallel_maintenance_workers | 2 | Sets the maximum number of parallel processes per maintenance operation. |
| max_parallel_workers | 0 | Sets the maximum number of parallel workers that can be active at one time. |
| max_parallel_workers_per_gather | 0 | Sets the maximum number of parallel processes per executor node. |
| max_worker_processes | 8 | Maximum number of concurrent worker processes. |
| parallel_leader_participation | on | Controls whether Gather and Gather Merge also run subplans. |
SELECT 5
explain(analyze,verbose,settings)
select*from test where partition_id>=1 and partition_id<2;
| QUERY PLAN |
|---|
| Append (cost=0.00..3967.70 rows=1061 width=2) (actual time=1.666..1.667 rows=0 loops=1) |
| -> Seq Scan on public.test1 test_1 (cost=0.00..3911.60 rows=1047 width=2) (actual time=1.653..1.653 rows=0 loops=1) |
| Output: test_1.partition_id |
| Filter: ((test_1.partition_id >= 1) AND (test_1.partition_id < 2)) |
| -> Seq Scan on public.test_default test_2 (cost=0.00..50.80 rows=14 width=2) (actual time=0.003..0.003 rows=0 loops=1) |
| Output: test_2.partition_id |
| Filter: ((test_2.partition_id >= 1) AND (test_2.partition_id < 2)) |
| Settings: max_parallel_workers_per_gather = '0', search_path = 'public, x_tablefunc, x_pg_trgm, x_intarray, x_hstore, x_tsm_system_rows, x_unaccent, x_ltree, x_postgis', max_parallel_workers = '0' |
| Planning Time: 0.062 ms |
| Execution Time: 1.677 ms |
EXPLAIN
explain(analyze,verbose,settings)
select*from test where partition_id=1;
| QUERY PLAN |
|---|
| Seq Scan on public.test1 test (cost=0.00..3388.00 rows=1047 width=2) (actual time=1.534..1.535 rows=0 loops=1) |
| Output: test.partition_id |
| Filter: (test.partition_id = 1) |
| Settings: max_parallel_workers_per_gather = '0', search_path = 'public, x_tablefunc, x_pg_trgm, x_intarray, x_hstore, x_tsm_system_rows, x_unaccent, x_ltree, x_postgis', max_parallel_workers = '0' |
| Planning Time: 0.040 ms |
| Execution Time: 1.543 ms |
EXPLAIN