What is the expected difference in performance between querying a all partitions of a partitioned table and non partitioned one? - Mailing list pgsql-general
| From | John Garrison |
|---|---|
| Subject | What is the expected difference in performance between querying a all partitions of a partitioned table and non partitioned one? |
| Date | |
| Msg-id | 8fbdc8f4-8228-4ee2-a2fc-3ba13aa5e5e2@www.fastmail.com Whole thread Raw |
| List | pgsql-general |
Hi there, my question is, shouldn't
a query that spans all partitions in a partionend table be roughly the same in performance as one on
a non partionend table? I'm getting roughly 50% slower performance on a partioned table.
I have tried this on a macbook and ubuntu server. More details are below.
The queries:
EXPLAIN (ANALYZE, VERBOSE, BUFFERS, COSTS) SELECT COUNT(*) FROM logs WHERE event = 'rewinded';
EXPLAIN (ANALYZE, VERBOSE, BUFFERS, COSTS) SELECT COUNT(*) FROM logs_partioned WHERE event = 'rewinded';
----
The results:
EXPLAIN (ANALYZE, VERBOSE, BUFFERS, COSTS) SELECT COUNT(*) FROM logs WHERE event = 'rewinded';
QUERY PLAN
Finalize Aggregate (cost=93673.28..93673.29 rows=1 width=8) (actual time=163.160..164.321 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=6929
-> Gather (cost=93672.86..93673.27 rows=4 width=8) (actual time=163.088..164.314 rows=4 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 4
Workers Launched: 3
Buffers: shared hit=6929
-> Partial Aggregate (cost=92672.86..92672.87 rows=1 width=8) (actual time=154.476..154.477 rows=1 loops=4)
Output: PARTIAL count(*)
Buffers: shared hit=6929
Worker 0: actual time=150.627..150.628 rows=1 loops=1
Buffers: shared hit=1725
Worker 1: actual time=152.368..152.368 rows=1 loops=1
Buffers: shared hit=1718
Worker 2: actual time=152.219..152.220 rows=1 loops=1
Buffers: shared hit=1727
-> Parallel Index Only Scan using logs_event_idx on public.logs (cost=0.57..87681.79 rows=1996428 width=0) (actual time=0.101..90.809 rows=1976526 loops=4)
Output: event
Index Cond: (logs.event = 'rewinded'::text)
Heap Fetches: 854
Buffers: shared hit=6929
Worker 0: actual time=0.155..88.693 rows=1959750 loops=1
Buffers: shared hit=1725
Worker 1: actual time=0.106..89.503 rows=1969110 loops=1
Buffers: shared hit=1718
Worker 2: actual time=0.102..89.430 rows=1973790 loops=1
Buffers: shared hit=1727
Planning Time: 0.242 ms
Execution Time: 164.383 ms
(30 rows)
Time: 165.105 ms
-------
EXPLAIN (ANALYZE, VERBOSE, BUFFERS, COSTS) SELECT COUNT(*) FROM logs_partioned WHERE event = 'rewinded';
QUERY PLAN
Finalize Aggregate (cost=110643.80..110643.81 rows=1 width=8) (actual time=249.562..250.597 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=6889
-> Gather (cost=110643.48..110643.79 rows=3 width=8) (actual time=249.522..250.593 rows=4 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 3
Workers Launched: 3
Buffers: shared hit=6889
-> Partial Aggregate (cost=109643.48..109643.49 rows=1 width=8) (actual time=240.213..240.214 rows=1 loops=4)
Output: PARTIAL count(*)
Buffers: shared hit=6889
Worker 0: actual time=237.259..237.260 rows=1 loops=1
Worker 0: actual time=237.259..237.260 rows=1 loops=1
Buffers: shared hit=1678
Worker 1: actual time=237.244..237.245 rows=1 loops=1
Buffers: shared hit=1768
Worker 2: actual time=237.304..237.305 rows=1 loops=1
Buffers: shared hit=1699
-> Parallel Append (cost=0.44..103381.36 rows=2504849 width=0) (actual time=0.132..183.045 rows=1976526 loops=4)
Buffers: shared hit=6889
Worker 0: actual time=0.171..179.435 rows=1951389 loops=1
Buffers: shared hit=1678
Worker 1: actual time=0.160..181.963 rows=1954680 loops=1
Buffers: shared hit=1768
Worker 2: actual time=0.159..181.714 rows=1976309 loops=1
Buffers: shared hit=1699
-> Parallel Index Only Scan using logs_partioned_p2_event_idx on public.logs_partioned_p2 logs_partioned_2 (cost=0.44..23064.91 rows=638123 width=0) (actual time=0.129..47.947 rows=981544 loops=2)
Index Cond: (logs_partioned_2.event = 'rewinded'::text)
Heap Fetches: 0
Buffers: shared hit=1690
Worker 0: actual time=0.171..95.322 rows=1951389 loops=1
Buffers: shared hit=1678
Worker 1: actual time=0.087..0.571 rows=11700 loops=1
Buffers: shared hit=12
-> Parallel Index Only Scan using logs_partioned_p4_event_idx on public.logs_partioned_p4 logs_partioned_4 (cost=0.44..22982.75 rows=635850 width=0) (actual time=0.054..31.451 rows=663060 loops=3)
Index Cond: (logs_partioned_4.event = 'rewinded'::text)
Heap Fetches: 0
Buffers: shared hit=1712
Worker 1: actual time=0.001..0.001 rows=0 loops=1
Buffers: shared hit=1
Worker 2: actual time=0.158..93.844 rows=1976309 loops=1
Buffers: shared hit=1699
-> Parallel Index Only Scan using logs_partioned_p3_event_idx on public.logs_partioned_p3 logs_partioned_3 (cost=0.44..22398.42 rows=610946 width=0) (actual time=0.082..49.059 rows=993703 loops=2)
Index Cond: (logs_partioned_3.event = 'rewinded'::text)
Heap Fetches: 876
Buffers: shared hit=1794
Worker 1: actual time=0.159..96.371 rows=1942980 loops=1
Buffers: shared hit=1755
-> Parallel Index Only Scan using logs_partioned_p1_event_idx on public.logs_partioned_p1 logs_partioned_1 (cost=0.44..22397.29 rows=619632 width=0) (actual time=0.025..95.237 rows=1965505 loops=1)
Index Cond: (logs_partioned_1.event = 'rewinded'::text)
Heap Fetches: 0
Buffers: shared hit=1689
-> Parallel Index Only Scan using logs_partioned_default_event_idx on public.logs_partioned_default logs_partioned_5 (cost=0.29..13.74 rows=543 width=0) (actual time=0.035..0.269 rows=923 loops=1)
Index Cond: (logs_partioned_5.event = 'rewinded'::text)
Heap Fetches: 0
Buffers: shared hit=4
Planning Time: 0.496 ms
Execution Time: 250.672 ms
Time: 251.689 ms
----
The specs and test setup:
PostgreSQL 13.2 on arm-apple-darwin20.3.0, compiled by Apple clang version 12.0.0 (clang-1200.0.32.29), 64-bit
Macbook Air M1 8GB RAM 8 core
Settings:
name | current_setting | source
----------------------------------+--------------------+----------------------
application_name | psql | client
checkpoint_completion_target | 0.9 | configuration file
checkpoint_timeout | 30min | configuration file
client_encoding | UTF8 | client
DateStyle | ISO, MDY | configuration file
default_text_search_config | pg_catalog.english | configuration file
dynamic_shared_memory_type | posix | configuration file
effective_cache_size | 4GB | configuration file
lc_messages | C | configuration file
lc_monetary | C | configuration file
lc_numeric | C | configuration file
lc_time | C | configuration file
log_directory | log | configuration file
log_timezone | America/Anchorage | configuration file
logging_collector | on | configuration file
maintenance_work_mem | 1GB | configuration file
max_connections | 8 | configuration file
max_parallel_maintenance_workers | 2 | configuration file
max_parallel_workers | 4 | configuration file
max_parallel_workers_per_gather | 4 | configuration file
max_stack_depth | 2MB | environment variable
max_wal_size | 20GB | configuration file
max_worker_processes | 4 | configuration file
min_wal_size | 80MB | configuration file
random_page_cost | 1.1 | configuration file
shared_buffers | 2GB | configuration file
TimeZone | America/Anchorage | configuration file
vacuum_cost_limit | 2000 | configuration file
wal_buffers | 64MB | configuration file
wal_compression | on | configuration file
work_mem | 256MB | configuration file
--------------------
-- Queries:
CREATE OR REPLACE FUNCTION random_event() RETURNS TEXT AS
$$
DECLARE
id int;
events text[] := '{started,stopped,paused,closed,entered,rewinded,looped,skipped,ignored,playing,clicked,hovered,seeked,ignored,fastforwarded,repeated}';
BEGIN
return events[1 + (random() * array_length(events, 1))];
END
$$ LANGUAGE plpgsql;
CREATE TABLE logs(created TIMESTAMPTZ DEFAULT now(), event TEXT);
INSERT INTO logs SELECT generate_series, random_event() FROM generate_series('2020-01-01', '2021-01-01', interval '250 milliseconds');
VACUUM ANALYZE logs;
CREATE INDEX ON logs(event);
CREATE TABLE logs_partioned(created TIMESTAMPTZ DEFAULT now(), event TEXT) PARTITION BY RANGE(created);
CREATE TABLE logs_partioned_p1 PARTITION OF logs_partioned FOR VALUES FROM ('2020-01-01') TO ('2020-04-01');
CREATE TABLE logs_partioned_p2 PARTITION OF logs_partioned FOR VALUES FROM ('2020-04-01') TO ('2020-07-01');
CREATE TABLE logs_partioned_p3 PARTITION OF logs_partioned FOR VALUES FROM ('2020-07-01') TO ('2020-10-01');
CREATE TABLE logs_partioned_p4 PARTITION OF logs_partioned FOR VALUES FROM ('2020-10-01') TO ('2021-01-01');
CREATE TABLE logs_partioned_default PARTITION OF logs_partioned DEFAULT;
INSERT INTO logs_partioned SELECT * FROM logs;
VACUUM ANALYZE logs_partioned;
CREATE INDEX ON logs_partioned(event);
pgsql-general by date: