Search Postgresql Archives

What is the expected difference in performance between querying a all partitions of a partitioned table and non partitioned one?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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);


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux