pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL 11 higher Planning time on Partitioned table
8+ messages / 3 participants
[nested] [flat]
* PostgreSQL 11 higher Planning time on Partitioned table
@ 2020-02-23 09:56 Ravi Garg <ravi.garg@yahoo.com>
2020-02-23 10:12 ` Re: PostgreSQL 11 higher Planning time on Partitioned table Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ravi Garg @ 2020-02-23 09:56 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi,
I am looking to Range Partition one of my table (i.e. TransactionLog) in PostgreSQL 11.While evaluating query performance difference between the un-partitioned and partitioned table I am getting huge difference in planning time. Planning time is very high on partitioned table.Similarly when I query by specifying partition name directly in query the planning time is much less **0.081 ms** as compared to when I query based on partition table (parent table) name in query, where planning time **6.231 ms** (Samples below).<br>
Below are the details, Let me know how can I improve query performance on partitioned table.
Following is the schema CREATE TABLE TransactionLog (
txid character varying(36) NOT NULL, txnDetails character varying(64), loggingtime timestamp(6) without time zone DEFAULT LOCALTIMESTAMP,) PARTITION BY RANGE(loggingtime);
CREATE TABLE IF NOT EXISTS TransactionLog_20200223 PARTITION OF TransactionLog FOR VALUES FROM ('2020-02-23') TO ('2020-02-24');CREATE UNIQUE INDEX TransactionLog_20200223_UnqTxId ON TransactionLog_20200223 (txnid);
Following is explain analyze result when I query Directly on partition. Planning time ~**0.080 ms** (average of 10 execution)postgres=> EXPLAIN (ANALYZE,VERBOSE,COSTS,BUFFERS,TIMING,SUMMARY) select txnDetails FROM mra_part.TransactionLog_20200223 WHERE txnid = 'febd139d-1b7f-4564-a004-1b3474e51756'; QUERY PLAN--------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using TransactionLog_20200223_UnqTxId on TransactionLog_20200223 (cost=0.57..4.61 rows=1 width=10) (actual time=0.039..0.040 rows=1 loops=1) Output: txnDetails Index Cond: ((TransactionLog_20200223.txnid)::text = 'febd139d-1b7f-4564-a004-1b3474e51756'::text) Buffers: shared hit=5 **Planning Time: 0.081 ms** Execution Time: 0.056 ms(6 rows)
Following is explain analyze result when I query by parent-table. Planning time **6.198 ms** (average of 10 execution)postgres=> EXPLAIN (ANALYZE,VERBOSE,COSTS,BUFFERS,TIMING,SUMMARY) select txnDetails FROM mtdauthlog WHERE txnid = 'febd139d-1b7f-4564-a004-1b3474e51756' AND loggingtime >= '2020-02-23'::timestamp without time zone AND loggingtime < '2020-02-24'::timestamp without time zone; QUERY PLAN---------------------------------------------------------------------------------------------------------------------------------------- Append (cost=0.57..4.62 rows=1 width=10) (actual time=0.036..0.037 rows=1 loops=1) Buffers: shared hit=5 -> Index Scan using TransactionLog_20200223_UnqTxId on TransactionLog_20200223 (cost=0.57..4.61 rows=1 width=10) (actual time=0.035..0.036 rows=1 loops=1) Output: TransactionLog_20200223.txnDetails Index Cond: ((TransactionLog_20200223.txnid)::text = 'febd139d-1b7f-4564-a004-1b3474e51756'::text) Filter: ((TransactionLog_20200223.loggingtime >= '2020-02-23 00:00:00'::timestamp without time zone) AND (TransactionLog_20200223.loggingtime < '2020-02-24 00:00:00'::timestamp without time zone)) Buffers: shared hit=5 **Planning Time: 6.231 ms** Execution Time: 0.076 ms(9 rows)
There are around ~200 child partitions. Partition pruning enabled.PostgreSQL Version: PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
Thanks and Regards,
Ravi Garg,
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PostgreSQL 11 higher Planning time on Partitioned table
2020-02-23 09:56 PostgreSQL 11 higher Planning time on Partitioned table Ravi Garg <ravi.garg@yahoo.com>
@ 2020-02-23 10:12 ` Justin Pryzby <pryzby@telsasoft.com>
2020-02-23 10:46 ` Re: PostgreSQL 11 higher Planning time on Partitioned table Justin Pryzby <pryzby@telsasoft.com>
2020-02-23 10:57 ` Re: PostgreSQL 11 higher Planning time on Partitioned table Ravi Garg <ravi.garg@yahoo.com>
0 siblings, 2 replies; 8+ messages in thread
From: Justin Pryzby @ 2020-02-23 10:12 UTC (permalink / raw)
To: Ravi Garg <ravi.garg@yahoo.com>; +Cc: pgsql-performance@lists.postgresql.org
On Sun, Feb 23, 2020 at 09:56:30AM +0000, Ravi Garg wrote:
> Hi,
> I am looking to Range Partition one of my table (i.e. TransactionLog) in PostgreSQL 11.While evaluating query performance difference between the un-partitioned and partitioned table I am getting huge difference in planning time. Planning time is very high on partitioned table.Similarly when I query by specifying partition name directly in query the planning time is much less **0.081 ms** as compared to when I query based on partition table (parent table) name in query, where planning time **6.231 ms** (Samples below).<br>
That's probably to be expected under pg11:
https://www.postgresql.org/docs/11/ddl-partitioning.html
|Too many partitions can mean longer query planning times...
|It is also important to consider the overhead of partitioning during query planning and execution. The query planner is generally able to handle partition hierarchies with up to a few hundred partitions fairly well, provided that typical queries allow the query planner to prune all but a small number of partitions. Planning times become longer and memory consumption becomes higher as more partitions are added
> There are around ~200 child partitions. Partition pruning enabled.PostgreSQL Version: PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
How large are the partitions and how many indexes each, and how large are they?
Each partition will be stat()ed and each index will be open()ed and read() for
every query. This was resolved in pg12:
https://commitfest.postgresql.org/21/1778/
--
Justin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PostgreSQL 11 higher Planning time on Partitioned table
2020-02-23 09:56 PostgreSQL 11 higher Planning time on Partitioned table Ravi Garg <ravi.garg@yahoo.com>
2020-02-23 10:12 ` Re: PostgreSQL 11 higher Planning time on Partitioned table Justin Pryzby <pryzby@telsasoft.com>
@ 2020-02-23 10:46 ` Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 0 replies; 8+ messages in thread
From: Justin Pryzby @ 2020-02-23 10:46 UTC (permalink / raw)
To: Ravi Garg <ravi.garg@yahoo.com>; +Cc: pgsql-performance@lists.postgresql.org
On Sun, Feb 23, 2020 at 04:12:09AM -0600, Justin Pryzby wrote:
> How large are the partitions and how many indexes each, and how large are they?
> Each partition will be stat()ed ... for every query.
I should have said that's every 1GB "segment" is stat()ed for every query.
> This was resolved in pg12:
> https://commitfest.postgresql.org/21/1778/
+ https://www.postgresql.org/about/featurematrix/detail/320/
--
Justin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PostgreSQL 11 higher Planning time on Partitioned table
2020-02-23 09:56 PostgreSQL 11 higher Planning time on Partitioned table Ravi Garg <ravi.garg@yahoo.com>
2020-02-23 10:12 ` Re: PostgreSQL 11 higher Planning time on Partitioned table Justin Pryzby <pryzby@telsasoft.com>
@ 2020-02-23 10:57 ` Ravi Garg <ravi.garg@yahoo.com>
2020-02-23 15:10 ` Re: PostgreSQL 11 higher Planning time on Partitioned table Justin Pryzby <pryzby@telsasoft.com>
2020-02-23 16:18 ` Re: PostgreSQL 11 higher Planning time on Partitioned table Imre Samu <pella.samu@gmail.com>
1 sibling, 2 replies; 8+ messages in thread
From: Ravi Garg @ 2020-02-23 10:57 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org
Hi Justin,
Thanks for response.
Unfortunately we will not be able to migrate to PG12 any time soon.
- There is only one index (unique index btree) on 'txnID' (i.e. transaction ID) character varying(36). Which we are creating on each partition.
- Our use case is limited to simple selects (we don't join with the other tables) however, we are expecting ~70 million records inserted per day and there would be couple of updates on each records where average record size would be ~ 1.5 KB.
- Currently we are thinking to have Daily partitions and as we need to keep 6 months of data thus 180 Partitions.However we have liberty to reduce the number of partitions to weekly/fortnightly/monthly, If we get comparable performance.
- We need to look current partition and previous partition for all of our use-cases/queries.
Can you please suggest what sort of combinations/partition strategy we can test considering data-volume/vacuum etc. Also let me know if some of the pg_settings can help us tuning this (I have attached my pg_settings).
Thanks and Regards,
Ravi Garg,
Mob : +91-98930-66610
On Sunday, 23 February, 2020, 03:42:13 pm IST, Justin Pryzby <pryzby@telsasoft.com> wrote:
On Sun, Feb 23, 2020 at 09:56:30AM +0000, Ravi Garg wrote:
> Hi,
> I am looking to Range Partition one of my table (i.e. TransactionLog) in PostgreSQL 11.While evaluating query performance difference between the un-partitioned and partitioned table I am getting huge difference in planning time. Planning time is very high on partitioned table.Similarly when I query by specifying partition name directly in query the planning time is much less **0.081 ms** as compared to when I query based on partition table (parent table) name in query, where planning time **6.231 ms** (Samples below).<br>
That's probably to be expected under pg11:
https://www.postgresql.org/docs/11/ddl-partitioning.html
|Too many partitions can mean longer query planning times...
|It is also important to consider the overhead of partitioning during query planning and execution. The query planner is generally able to handle partition hierarchies with up to a few hundred partitions fairly well, provided that typical queries allow the query planner to prune all but a small number of partitions. Planning times become longer and memory consumption becomes higher as more partitions are added
> There are around ~200 child partitions. Partition pruning enabled.PostgreSQL Version: PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
How large are the partitions and how many indexes each, and how large are they?
Each partition will be stat()ed and each index will be open()ed and read() for
every query. This was resolved in pg12:
https://commitfest.postgresql.org/21/1778/
--
Justin
postgres=# select name, setting from pg_settings order by name desc;
name | setting
----------------------------------------+-----------------------------------------
zero_damaged_pages | off
xmloption | content
xmlbinary | base64
work_mem | 4096
wal_writer_flush_after | 128
wal_writer_delay | 200
wal_sync_method | fdatasync
wal_sender_timeout | 60000
wal_segment_size | 1073741824
wal_retrieve_retry_interval | 5000
wal_receiver_timeout | 60000
wal_receiver_status_interval | 10
wal_log_hints | on
wal_level | replica
wal_keep_segments | 50
wal_consistency_checking |
wal_compression | off
wal_buffers | 8192
wal_block_size | 8192
vacuum_multixact_freeze_table_age | 150000000
vacuum_multixact_freeze_min_age | 5000000
vacuum_freeze_table_age | 150000000
vacuum_freeze_min_age | 50000000
vacuum_defer_cleanup_age | 0
vacuum_cost_page_miss | 10
vacuum_cost_page_hit | 1
vacuum_cost_page_dirty | 20
vacuum_cost_limit | 800
vacuum_cost_delay | 0
vacuum_cleanup_index_scale_factor | 0.1
update_process_title | on
unix_socket_permissions | 0777
unix_socket_group |
unix_socket_directories | /tmp
transform_null_equals | off
transaction_read_only | off
transaction_isolation | read committed
transaction_deferrable | off
track_io_timing | on
track_functions | all
track_counts | on
track_commit_timestamp | off
track_activity_query_size | 1024
track_activities | on
trace_sort | off
trace_recovery_messages | log
trace_notify | off
timezone_abbreviations | Default
TimeZone | UTC
temp_tablespaces |
temp_file_limit | -1
temp_buffers | 1024
tcp_keepalives_interval | 75
tcp_keepalives_idle | 7200
tcp_keepalives_count | 9
syslog_split_messages | on
syslog_sequence_numbers | on
syslog_ident | postgres
syslog_facility | local0
synchronous_standby_names |
synchronous_commit | on
synchronize_seqscans | on
superuser_reserved_connections | 3
stats_temp_directory | pg_stat_tmp
statement_timeout | 0
standard_conforming_strings | on
ssl_prefer_server_ciphers | on
ssl_passphrase_command_supports_reload | off
ssl_passphrase_command |
ssl_key_file | server.key
ssl_ecdh_curve | prime256v1
ssl_dh_params_file |
ssl_crl_file |
ssl_ciphers | HIGH:MEDIUM:+3DES:!aNULL
ssl_cert_file | server.crt
ssl_ca_file |
ssl | off
shared_preload_libraries |
shared_buffers | 1048576
session_replication_role | origin
session_preload_libraries |
server_version_num | 110007
server_version | 11.7
server_encoding | UTF8
seq_page_cost | 1
segment_size | 131072
search_path | "$user", public
row_security | on
restart_after_crash | on
random_page_cost | 2
quote_all_identifiers | off
pre_auth_delay | 0
post_auth_delay | 0
port | 5432
password_encryption | md5
parallel_tuple_cost | 0.1
parallel_setup_cost | 1000
parallel_leader_participation | on
operator_precedence_warning | off
old_snapshot_threshold | -1
min_wal_size | 20480
min_parallel_table_scan_size | 1024
min_parallel_index_scan_size | 64
max_worker_processes | 8
max_wal_size | 61440
max_wal_senders | 10
max_sync_workers_per_subscription | 2
max_standby_streaming_delay | 30000
max_standby_archive_delay | 30000
max_stack_depth | 2048
max_replication_slots | 10
max_prepared_transactions | 0
max_pred_locks_per_transaction | 64
max_pred_locks_per_relation | -2
max_pred_locks_per_page | 2
max_parallel_workers_per_gather | 2
max_parallel_workers | 8
max_parallel_maintenance_workers | 2
max_logical_replication_workers | 6
max_locks_per_transaction | 64
max_index_keys | 32
max_identifier_length | 63
max_function_args | 100
max_files_per_process | 1000
max_connections | 3000
maintenance_work_mem | 1048576
log_truncate_on_rotation | on
log_timezone | UTC
log_temp_files | 0
log_statement_stats | off
log_statement | mod
log_rotation_size | 0
log_rotation_age | 1440
log_replication_commands | off
log_planner_stats | off
log_parser_stats | off
log_min_messages | warning
log_min_error_statement | error
log_min_duration_statement | 0
log_lock_waits | on
log_line_prefix | [%p-%s-%c-%l-%h-%u-%d-%a-%m]
log_hostname | off
logging_collector | on
log_filename | postgresql-%a.log
log_file_mode | 0600
log_executor_stats | off
log_error_verbosity | default
log_duration | on
log_disconnections | on
log_directory | /u02/pglogs
log_destination | stderr
log_connections | on
log_checkpoints | on
log_autovacuum_min_duration | 0
lo_compat_privileges | off
lock_timeout | 0
local_preload_libraries |
listen_addresses | 0.0.0.0
lc_time | en_US.UTF-8
lc_numeric | en_US.UTF-8
lc_monetary | en_US.UTF-8
lc_messages | C
lc_ctype | en_US.UTF-8
lc_collate | en_US.UTF-8
krb_server_keyfile | FILE:/etc/sysconfig/pgsql/krb5.keytab
krb_caseins_users | off
join_collapse_limit | 8
jit_tuple_deforming | on
jit_provider | llvmjit
jit_profiling_support | off
jit_optimize_above_cost | 500000
jit_inline_above_cost | 500000
jit_expressions | on
jit_dump_bitcode | off
jit_debugging_support | off
jit_above_cost | 100000
jit | off
IntervalStyle | postgres
integer_datetimes | on
ignore_system_indexes | off
ignore_checksum_failure | off
idle_in_transaction_session_timeout | 0
ident_file | /opt/PostgreSQL/11/data/pg_ident.conf
huge_pages | try
hot_standby_feedback | off
hot_standby | on
hba_file | /opt/PostgreSQL/11/data/pg_hba.conf
gin_pending_list_limit | 4096
gin_fuzzy_search_limit | 0
geqo_threshold | 12
geqo_selection_bias | 2
geqo_seed | 0
geqo_pool_size | 0
geqo_generations | 0
geqo_effort | 5
geqo | on
full_page_writes | on
fsync | on
from_collapse_limit | 8
force_parallel_mode | off
extra_float_digits | 0
external_pid_file |
exit_on_error | off
event_source | PostgreSQL
escape_string_warning | on
enable_tidscan | on
enable_sort | on
enable_seqscan | on
enable_partitionwise_join | off
enable_partitionwise_aggregate | off
enable_partition_pruning | on
enable_parallel_hash | on
enable_parallel_append | on
enable_nestloop | on
enable_mergejoin | on
enable_material | on
enable_indexscan | on
enable_indexonlyscan | on
enable_hashjoin | on
enable_hashagg | on
enable_gathermerge | on
enable_bitmapscan | on
effective_io_concurrency | 1
effective_cache_size | 524288
dynamic_shared_memory_type | posix
dynamic_library_path | $libdir
default_with_oids | off
default_transaction_read_only | off
default_transaction_isolation | read committed
default_transaction_deferrable | off
default_text_search_config | pg_catalog.english
default_tablespace |
default_statistics_target | 5000
debug_print_rewritten | off
debug_print_plan | off
debug_print_parse | off
debug_pretty_print | on
debug_assertions | off
deadlock_timeout | 1000
db_user_namespace | off
DateStyle | ISO, MDY
data_sync_retry | off
data_directory_mode | 0700
data_directory | /opt/PostgreSQL/11/data
data_checksums | off
cursor_tuple_fraction | 0.1
cpu_tuple_cost | 0.03
cpu_operator_cost | 0.0025
cpu_index_tuple_cost | 0.005
constraint_exclusion | partition
config_file | /opt/PostgreSQL/11/data/postgresql.conf
commit_siblings | 5
commit_delay | 0
cluster_name | mracluster
client_min_messages | notice
client_encoding | UTF8
checkpoint_warning | 60
checkpoint_timeout | 300
checkpoint_flush_after | 32
checkpoint_completion_target | 0.9
check_function_bodies | on
bytea_output | hex
bonjour_name |
bonjour | off
block_size | 8192
bgwriter_lru_multiplier | 4
bgwriter_lru_maxpages | 1000
bgwriter_flush_after | 64
bgwriter_delay | 100
backslash_quote | safe_encoding
backend_flush_after | 0
autovacuum_work_mem | -1
autovacuum_vacuum_threshold | 50
autovacuum_vacuum_scale_factor | 0.05
autovacuum_vacuum_cost_limit | -1
autovacuum_vacuum_cost_delay | 10
autovacuum_naptime | 5
autovacuum_multixact_freeze_max_age | 400000000
autovacuum_max_workers | 3
autovacuum_freeze_max_age | 200000000
autovacuum_analyze_threshold | 50
autovacuum_analyze_scale_factor | 0.05
autovacuum | on
authentication_timeout | 60
array_nulls | on
archive_timeout | 0
archive_mode | off
archive_command | (disabled)
application_name | psql
allow_system_table_mods | off
(290 rows)
Attachments:
[text/plain] pg_settings.txt (13.9K, ../../1169437250.5002073.1582455449880@mail.yahoo.com/3-pg_settings.txt)
download | inline:
postgres=# select name, setting from pg_settings order by name desc;
name | setting
----------------------------------------+-----------------------------------------
zero_damaged_pages | off
xmloption | content
xmlbinary | base64
work_mem | 4096
wal_writer_flush_after | 128
wal_writer_delay | 200
wal_sync_method | fdatasync
wal_sender_timeout | 60000
wal_segment_size | 1073741824
wal_retrieve_retry_interval | 5000
wal_receiver_timeout | 60000
wal_receiver_status_interval | 10
wal_log_hints | on
wal_level | replica
wal_keep_segments | 50
wal_consistency_checking |
wal_compression | off
wal_buffers | 8192
wal_block_size | 8192
vacuum_multixact_freeze_table_age | 150000000
vacuum_multixact_freeze_min_age | 5000000
vacuum_freeze_table_age | 150000000
vacuum_freeze_min_age | 50000000
vacuum_defer_cleanup_age | 0
vacuum_cost_page_miss | 10
vacuum_cost_page_hit | 1
vacuum_cost_page_dirty | 20
vacuum_cost_limit | 800
vacuum_cost_delay | 0
vacuum_cleanup_index_scale_factor | 0.1
update_process_title | on
unix_socket_permissions | 0777
unix_socket_group |
unix_socket_directories | /tmp
transform_null_equals | off
transaction_read_only | off
transaction_isolation | read committed
transaction_deferrable | off
track_io_timing | on
track_functions | all
track_counts | on
track_commit_timestamp | off
track_activity_query_size | 1024
track_activities | on
trace_sort | off
trace_recovery_messages | log
trace_notify | off
timezone_abbreviations | Default
TimeZone | UTC
temp_tablespaces |
temp_file_limit | -1
temp_buffers | 1024
tcp_keepalives_interval | 75
tcp_keepalives_idle | 7200
tcp_keepalives_count | 9
syslog_split_messages | on
syslog_sequence_numbers | on
syslog_ident | postgres
syslog_facility | local0
synchronous_standby_names |
synchronous_commit | on
synchronize_seqscans | on
superuser_reserved_connections | 3
stats_temp_directory | pg_stat_tmp
statement_timeout | 0
standard_conforming_strings | on
ssl_prefer_server_ciphers | on
ssl_passphrase_command_supports_reload | off
ssl_passphrase_command |
ssl_key_file | server.key
ssl_ecdh_curve | prime256v1
ssl_dh_params_file |
ssl_crl_file |
ssl_ciphers | HIGH:MEDIUM:+3DES:!aNULL
ssl_cert_file | server.crt
ssl_ca_file |
ssl | off
shared_preload_libraries |
shared_buffers | 1048576
session_replication_role | origin
session_preload_libraries |
server_version_num | 110007
server_version | 11.7
server_encoding | UTF8
seq_page_cost | 1
segment_size | 131072
search_path | "$user", public
row_security | on
restart_after_crash | on
random_page_cost | 2
quote_all_identifiers | off
pre_auth_delay | 0
post_auth_delay | 0
port | 5432
password_encryption | md5
parallel_tuple_cost | 0.1
parallel_setup_cost | 1000
parallel_leader_participation | on
operator_precedence_warning | off
old_snapshot_threshold | -1
min_wal_size | 20480
min_parallel_table_scan_size | 1024
min_parallel_index_scan_size | 64
max_worker_processes | 8
max_wal_size | 61440
max_wal_senders | 10
max_sync_workers_per_subscription | 2
max_standby_streaming_delay | 30000
max_standby_archive_delay | 30000
max_stack_depth | 2048
max_replication_slots | 10
max_prepared_transactions | 0
max_pred_locks_per_transaction | 64
max_pred_locks_per_relation | -2
max_pred_locks_per_page | 2
max_parallel_workers_per_gather | 2
max_parallel_workers | 8
max_parallel_maintenance_workers | 2
max_logical_replication_workers | 6
max_locks_per_transaction | 64
max_index_keys | 32
max_identifier_length | 63
max_function_args | 100
max_files_per_process | 1000
max_connections | 3000
maintenance_work_mem | 1048576
log_truncate_on_rotation | on
log_timezone | UTC
log_temp_files | 0
log_statement_stats | off
log_statement | mod
log_rotation_size | 0
log_rotation_age | 1440
log_replication_commands | off
log_planner_stats | off
log_parser_stats | off
log_min_messages | warning
log_min_error_statement | error
log_min_duration_statement | 0
log_lock_waits | on
log_line_prefix | [%p-%s-%c-%l-%h-%u-%d-%a-%m]
log_hostname | off
logging_collector | on
log_filename | postgresql-%a.log
log_file_mode | 0600
log_executor_stats | off
log_error_verbosity | default
log_duration | on
log_disconnections | on
log_directory | /u02/pglogs
log_destination | stderr
log_connections | on
log_checkpoints | on
log_autovacuum_min_duration | 0
lo_compat_privileges | off
lock_timeout | 0
local_preload_libraries |
listen_addresses | 0.0.0.0
lc_time | en_US.UTF-8
lc_numeric | en_US.UTF-8
lc_monetary | en_US.UTF-8
lc_messages | C
lc_ctype | en_US.UTF-8
lc_collate | en_US.UTF-8
krb_server_keyfile | FILE:/etc/sysconfig/pgsql/krb5.keytab
krb_caseins_users | off
join_collapse_limit | 8
jit_tuple_deforming | on
jit_provider | llvmjit
jit_profiling_support | off
jit_optimize_above_cost | 500000
jit_inline_above_cost | 500000
jit_expressions | on
jit_dump_bitcode | off
jit_debugging_support | off
jit_above_cost | 100000
jit | off
IntervalStyle | postgres
integer_datetimes | on
ignore_system_indexes | off
ignore_checksum_failure | off
idle_in_transaction_session_timeout | 0
ident_file | /opt/PostgreSQL/11/data/pg_ident.conf
huge_pages | try
hot_standby_feedback | off
hot_standby | on
hba_file | /opt/PostgreSQL/11/data/pg_hba.conf
gin_pending_list_limit | 4096
gin_fuzzy_search_limit | 0
geqo_threshold | 12
geqo_selection_bias | 2
geqo_seed | 0
geqo_pool_size | 0
geqo_generations | 0
geqo_effort | 5
geqo | on
full_page_writes | on
fsync | on
from_collapse_limit | 8
force_parallel_mode | off
extra_float_digits | 0
external_pid_file |
exit_on_error | off
event_source | PostgreSQL
escape_string_warning | on
enable_tidscan | on
enable_sort | on
enable_seqscan | on
enable_partitionwise_join | off
enable_partitionwise_aggregate | off
enable_partition_pruning | on
enable_parallel_hash | on
enable_parallel_append | on
enable_nestloop | on
enable_mergejoin | on
enable_material | on
enable_indexscan | on
enable_indexonlyscan | on
enable_hashjoin | on
enable_hashagg | on
enable_gathermerge | on
enable_bitmapscan | on
effective_io_concurrency | 1
effective_cache_size | 524288
dynamic_shared_memory_type | posix
dynamic_library_path | $libdir
default_with_oids | off
default_transaction_read_only | off
default_transaction_isolation | read committed
default_transaction_deferrable | off
default_text_search_config | pg_catalog.english
default_tablespace |
default_statistics_target | 5000
debug_print_rewritten | off
debug_print_plan | off
debug_print_parse | off
debug_pretty_print | on
debug_assertions | off
deadlock_timeout | 1000
db_user_namespace | off
DateStyle | ISO, MDY
data_sync_retry | off
data_directory_mode | 0700
data_directory | /opt/PostgreSQL/11/data
data_checksums | off
cursor_tuple_fraction | 0.1
cpu_tuple_cost | 0.03
cpu_operator_cost | 0.0025
cpu_index_tuple_cost | 0.005
constraint_exclusion | partition
config_file | /opt/PostgreSQL/11/data/postgresql.conf
commit_siblings | 5
commit_delay | 0
cluster_name | mracluster
client_min_messages | notice
client_encoding | UTF8
checkpoint_warning | 60
checkpoint_timeout | 300
checkpoint_flush_after | 32
checkpoint_completion_target | 0.9
check_function_bodies | on
bytea_output | hex
bonjour_name |
bonjour | off
block_size | 8192
bgwriter_lru_multiplier | 4
bgwriter_lru_maxpages | 1000
bgwriter_flush_after | 64
bgwriter_delay | 100
backslash_quote | safe_encoding
backend_flush_after | 0
autovacuum_work_mem | -1
autovacuum_vacuum_threshold | 50
autovacuum_vacuum_scale_factor | 0.05
autovacuum_vacuum_cost_limit | -1
autovacuum_vacuum_cost_delay | 10
autovacuum_naptime | 5
autovacuum_multixact_freeze_max_age | 400000000
autovacuum_max_workers | 3
autovacuum_freeze_max_age | 200000000
autovacuum_analyze_threshold | 50
autovacuum_analyze_scale_factor | 0.05
autovacuum | on
authentication_timeout | 60
array_nulls | on
archive_timeout | 0
archive_mode | off
archive_command | (disabled)
application_name | psql
allow_system_table_mods | off
(290 rows)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PostgreSQL 11 higher Planning time on Partitioned table
2020-02-23 09:56 PostgreSQL 11 higher Planning time on Partitioned table Ravi Garg <ravi.garg@yahoo.com>
2020-02-23 10:12 ` Re: PostgreSQL 11 higher Planning time on Partitioned table Justin Pryzby <pryzby@telsasoft.com>
2020-02-23 10:57 ` Re: PostgreSQL 11 higher Planning time on Partitioned table Ravi Garg <ravi.garg@yahoo.com>
@ 2020-02-23 15:10 ` Justin Pryzby <pryzby@telsasoft.com>
2020-02-24 19:40 ` Re: PostgreSQL 11 higher Planning time on Partitioned table Ravi Garg <ravi.garg@yahoo.com>
1 sibling, 1 reply; 8+ messages in thread
From: Justin Pryzby @ 2020-02-23 15:10 UTC (permalink / raw)
To: Ravi Garg <ravi.garg@yahoo.com>; +Cc: pgsql-performance@lists.postgresql.org
On Sun, Feb 23, 2020 at 10:57:29AM +0000, Ravi Garg wrote:
> - Currently we are thinking to have Daily partitions and as we need to keep 6 months of data thus 180 Partitions.However we have liberty to reduce the number of partitions to weekly/fortnightly/monthly, If we get comparable performance.
I didn't hear how large the tables and indexes are.
> - We need to look current partition and previous partition for all of our use-cases/queries.
Do you mean that a given query is only going to hit 2 partitions ? Or do you
mean that all but the most recent 2 partitions are "archival" and won't be
needed by future queries ?
> Can you please suggest what sort of combinations/partition strategy we can test considering data-volume/vacuum etc. Also let me know if some of the pg_settings can help us tuning this (I have attached my pg_settings).
You should determine what an acceptable planning speed is, or the best balance
of planning/execution time. Try to detach half your current partitions and, if
that gives acceptable performance, then partition by day/2 or more. You could
make a graph of (planning and total) time vs npartitions, since I think it's
likely to be nonlinear.
I believe others have reported improved performance under v11 with larger
numbers of partitions, by using "partitions of partitions". So you could try
making partitions by month themselves partitioned by day.
> - Our use case is limited to simple selects (we don't join with the other
> tables) however, we are expecting ~70 million records inserted per day
> and there would be couple of updates on each records where average record
> size would be ~ 1.5 KB.
> shared_buffers | 1048576
If you care about INSERT performance, you probably need to make at least a
single partition's index fit within shared_buffers (or set shared_buffers such
that it fits). Use transactions around your inserts. If your speed is not
limited by I/O, you could further use multiple VALUES(),() inserts, or maybe
prepared statements. Maybe synchronous_commit=off.
If you care about (consistent) SELECT performance, you should consider
VACUUMing the tables after bulk inserts, to set hint bits (and since
non-updated tuples won't be hit by autovacuum). Or maybe VACUUM FREEZE to
freeze tuples (since it sounds like a typical page is unlikely to ever be
updated).
--
Justin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PostgreSQL 11 higher Planning time on Partitioned table
2020-02-23 09:56 PostgreSQL 11 higher Planning time on Partitioned table Ravi Garg <ravi.garg@yahoo.com>
2020-02-23 10:12 ` Re: PostgreSQL 11 higher Planning time on Partitioned table Justin Pryzby <pryzby@telsasoft.com>
2020-02-23 10:57 ` Re: PostgreSQL 11 higher Planning time on Partitioned table Ravi Garg <ravi.garg@yahoo.com>
2020-02-23 15:10 ` Re: PostgreSQL 11 higher Planning time on Partitioned table Justin Pryzby <pryzby@telsasoft.com>
@ 2020-02-24 19:40 ` Ravi Garg <ravi.garg@yahoo.com>
0 siblings, 0 replies; 8+ messages in thread
From: Ravi Garg @ 2020-02-24 19:40 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org
Hi Justin,
>I didn't hear how large the tables and indexes are.+-------------------------------------------+------------------+--------------------------------------------+| table_name | pg_relation_size | pg_total_relation_size - pg_relation_size |+-------------------------------------------+------------------+--------------------------------------------+| TransactionLog_20200213 | 95646646272 | 4175699968 || TransactionLog_20200212 | 95573344256 | 4133617664 || TransactionLog_20200211 | 91477336064 | 3956457472 || TransactionLog_20200210 | 8192000000 | 354344960 || TransactionLog_20200214 | 6826672128 | 295288832 || TransactionLog_20200220 | 1081393152 | 89497600 || pg_catalogpg_attribute | 3088384 | 2220032 || TransactionLog_20190925 | 1368064 | 90112 (174 such partitions) |+-------------------------------------------+------------------+--------------------------------------------+ > Do you mean that a given query is only going to hit 2 partitions ? Or do you> mean that all but the most recent 2 partitions are "archival" and won't be
> needed by future queries ?
Yes all queries will hit only 2 partitions (e.g. if we do daily partition, queries will hit only today's and yesterday's partition).
> You should determine what an acceptable planning speed is, or the best balance> of planning/execution time. Try to detach half your current partitions and, if> that gives acceptable performance, then partition by day/2 or more. You could> make a graph of (planning and total) time vs npartitions, since I think it's> likely to be nonlinear.> I believe others have reported improved performance under v11 with larger> numbers of partitions, by using "partitions of partitions". So you could try> making partitions by month themselves partitioned by day.
FYI, these are the observations I am getting with various number of partition and a multilevel partition with respect to Un-Partitioned.+---------------+----------------------+----------------+---------------+--------------+---------------+--------------+---------------+--------------+| Testcase | Partition Count | Records in | Select | Select | Update | Update | insert | insert || | | each Partition | planning (ms) | execute (ms) | planning (ms) | execute (ms) | planning (ms) | execute (ms) |+---------------+----------------------+----------------+---------------+--------------+---------------+--------------+---------------+--------------+| Single Level | 6 | 1000 | 1.162 | 0.045 | 2.112 | 0.115 | 1.261 | 0.178 || Partition | 30 | 1000 | 2.879 | 0.049 | 5.146 | 0.13 | 1.243 | 0.211 || | 200 | 1000 | 18.479 | 0.087 | 31.385 | 0.18 | 1.253 | 0.468 |+---------------+----------------------+----------------+---------------+--------------+---------------+--------------+---------------+--------------+| Multi Level | 6 Partition having | 1000 | 3.6032 | 0.0695 | x | x | x | x || Partition | 30 subpartition each | | | | | | | |+---------------+----------------------+----------------+---------------+--------------+---------------+--------------+---------------+--------------+| UnPartitioned | NA | 430 Million | 0.0875 | 0.0655 | x | x | x | x |+---------------+----------------------+----------------+---------------+--------------+---------------+--------------+---------------+--------------+
> If you care about INSERT performance, you probably need to make at least a> single partition's index fit within shared_buffers (or set shared_buffers such> that it fits). Use transactions around your inserts. If your speed is not> limited by I/O, you could further use multiple VALUES(),() inserts, or maybe> prepared statements. Maybe synchronous_commit=off.> > If you care about (consistent) SELECT performance, you should consider> VACUUMing the tables after bulk inserts, to set hint bits (and since> non-updated tuples won't be hit by autovacuum). Or maybe VACUUM FREEZE to> freeze tuples (since it sounds like a typical page is unlikely to ever be> updated).
Sure, I'll evaluate these settings, thanks.
Thanks and Regards,
Ravi Garg
On Sunday, 23 February, 2020, 08:40:58 pm IST, Justin Pryzby <pryzby@telsasoft.com> wrote:
On Sun, Feb 23, 2020 at 10:57:29AM +0000, Ravi Garg wrote:
> - Currently we are thinking to have Daily partitions and as we need to keep 6 months of data thus 180 Partitions.However we have liberty to reduce the number of partitions to weekly/fortnightly/monthly, If we get comparable performance.
I didn't hear how large the tables and indexes are.
> - We need to look current partition and previous partition for all of our use-cases/queries.
Do you mean that a given query is only going to hit 2 partitions ? Or do you
mean that all but the most recent 2 partitions are "archival" and won't be
needed by future queries ?
> Can you please suggest what sort of combinations/partition strategy we can test considering data-volume/vacuum etc. Also let me know if some of the pg_settings can help us tuning this (I have attached my pg_settings).
You should determine what an acceptable planning speed is, or the best balance
of planning/execution time. Try to detach half your current partitions and, if
that gives acceptable performance, then partition by day/2 or more. You could
make a graph of (planning and total) time vs npartitions, since I think it's
likely to be nonlinear.
I believe others have reported improved performance under v11 with larger
numbers of partitions, by using "partitions of partitions". So you could try
making partitions by month themselves partitioned by day.
> - Our use case is limited to simple selects (we don't join with the other
> tables) however, we are expecting ~70 million records inserted per day
> and there would be couple of updates on each records where average record
> size would be ~ 1.5 KB.
> shared_buffers | 1048576
If you care about INSERT performance, you probably need to make at least a
single partition's index fit within shared_buffers (or set shared_buffers such
that it fits). Use transactions around your inserts. If your speed is not
limited by I/O, you could further use multiple VALUES(),() inserts, or maybe
prepared statements. Maybe synchronous_commit=off.
If you care about (consistent) SELECT performance, you should consider
VACUUMing the tables after bulk inserts, to set hint bits (and since
non-updated tuples won't be hit by autovacuum). Or maybe VACUUM FREEZE to
freeze tuples (since it sounds like a typical page is unlikely to ever be
updated).
--
Justin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PostgreSQL 11 higher Planning time on Partitioned table
2020-02-23 09:56 PostgreSQL 11 higher Planning time on Partitioned table Ravi Garg <ravi.garg@yahoo.com>
2020-02-23 10:12 ` Re: PostgreSQL 11 higher Planning time on Partitioned table Justin Pryzby <pryzby@telsasoft.com>
2020-02-23 10:57 ` Re: PostgreSQL 11 higher Planning time on Partitioned table Ravi Garg <ravi.garg@yahoo.com>
@ 2020-02-23 16:18 ` Imre Samu <pella.samu@gmail.com>
2020-02-24 19:44 ` Re: PostgreSQL 11 higher Planning time on Partitioned table Ravi Garg <ravi.garg@yahoo.com>
1 sibling, 1 reply; 8+ messages in thread
From: Imre Samu @ 2020-02-23 16:18 UTC (permalink / raw)
To: Ravi Garg <ravi.garg@yahoo.com>; +Cc: pgsql-performance@lists.postgresql.org
> ... txid character varying(36) NOT NULL,
> ... WHERE txnid = 'febd139d-1b7f-4564-a004-1b3474e51756'
> There is only one index (unique index btree) on 'txnID' (i.e. transaction
ID) character varying(36). Which we are creating on each partition.
IF txnid is real UUID , then you can test the
https://www.postgresql.org/docs/11/datatype-uuid.html performance
see
https://stackoverflow.com/questions/29880083/postgresql-uuid-type-performance
imho: it should be better.
best,
Imre
Ravi Garg <ravi.garg@yahoo.com> ezt írta (időpont: 2020. febr. 23., V,
11:57):
> Hi Justin,
>
> Thanks for response.
>
> Unfortunately we will not be able to migrate to PG12 any time soon.
>
> - There is only one index (unique index btree) on 'txnID' (i.e.
> transaction ID) character varying(36). Which we are creating on each
> partition.
> - Our use case is limited to simple selects (we don't join with the
> other tables) however, we are expecting ~70 million records inserted
> per day and there would be couple of updates on each records where average
> record size would be ~ 1.5 KB.
> - Currently we are thinking to have Daily partitions and as we need to
> keep 6 months of data thus 180 Partitions.However we have liberty to reduce
> the number of partitions to weekly/fortnightly/monthly, If we get
> comparable performance.
> - We need to look current partition and previous partition for all of
> our use-cases/queries.
>
> Can you please suggest what sort of combinations/partition strategy we can
> test considering data-volume/vacuum etc. Also let me know if some of the
> pg_settings can help us tuning this (I have attached my pg_settings).
>
>
> Thanks and Regards,
> Ravi Garg,
> Mob : +91-98930-66610
>
>
> On Sunday, 23 February, 2020, 03:42:13 pm IST, Justin Pryzby <
> pryzby@telsasoft.com> wrote:
>
>
> On Sun, Feb 23, 2020 at 09:56:30AM +0000, Ravi Garg wrote:
> > Hi,
> > I am looking to Range Partition one of my table (i.e. TransactionLog) in
> PostgreSQL 11.While evaluating query performance difference between the
> un-partitioned and partitioned table I am getting huge difference in
> planning time. Planning time is very high on partitioned table.Similarly
> when I query by specifying partition name directly in query the planning
> time is much less **0.081 ms** as compared to when I query based on
> partition table (parent table) name in query, where planning time **6.231
> ms** (Samples below).<br>
>
> That's probably to be expected under pg11:
>
> https://www.postgresql.org/docs/11/ddl-partitioning.html
> |Too many partitions can mean longer query planning times...
> |It is also important to consider the overhead of partitioning during
> query planning and execution. The query planner is generally able to handle
> partition hierarchies with up to a few hundred partitions fairly well,
> provided that typical queries allow the query planner to prune all but a
> small number of partitions. Planning times become longer and memory
> consumption becomes higher as more partitions are added
>
>
> > There are around ~200 child partitions. Partition pruning
> enabled.PostgreSQL Version: PostgreSQL 11.7 on x86_64-pc-linux-gnu,
> compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
>
>
> How large are the partitions and how many indexes each, and how large are
> they?
> Each partition will be stat()ed and each index will be open()ed and read()
> for
> every query. This was resolved in pg12:
> https://commitfest.postgresql.org/21/1778/
>
> --
> Justin
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PostgreSQL 11 higher Planning time on Partitioned table
2020-02-23 09:56 PostgreSQL 11 higher Planning time on Partitioned table Ravi Garg <ravi.garg@yahoo.com>
2020-02-23 10:12 ` Re: PostgreSQL 11 higher Planning time on Partitioned table Justin Pryzby <pryzby@telsasoft.com>
2020-02-23 10:57 ` Re: PostgreSQL 11 higher Planning time on Partitioned table Ravi Garg <ravi.garg@yahoo.com>
2020-02-23 16:18 ` Re: PostgreSQL 11 higher Planning time on Partitioned table Imre Samu <pella.samu@gmail.com>
@ 2020-02-24 19:44 ` Ravi Garg <ravi.garg@yahoo.com>
0 siblings, 0 replies; 8+ messages in thread
From: Ravi Garg @ 2020-02-24 19:44 UTC (permalink / raw)
To: Imre Samu <pella.samu@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
> IF txnid is real UUID , then you can test the https://www.postgresql.org/docs/11/datatype-uuid.html performance> see https://stackoverflow.com/questions/29880083/postgresql-uuid-type-performance; imho: it should be better.
Sure, thanks Imre
Thanks and Regards,
Ravi Garg
On Sunday, 23 February, 2020, 09:49:00 pm IST, Imre Samu <pella.samu@gmail.com> wrote:
> ... txid character varying(36) NOT NULL,
> ... WHERE txnid = 'febd139d-1b7f-4564-a004-1b3474e51756'> There is only one index (unique index btree) on 'txnID' (i.e. transaction ID) character varying(36). Which we are creating on each partition.
IF txnid is real UUID , then you can test the https://www.postgresql.org/docs/11/datatype-uuid.html performancesee https://stackoverflow.com/quest...: it should be better.
best, Imre
Ravi Garg <ravi.garg@yahoo.com> ezt írta (időpont: 2020. febr. 23., V, 11:57):
Hi Justin,
Thanks for response.
Unfortunately we will not be able to migrate to PG12 any time soon.
- There is only one index (unique index btree) on 'txnID' (i.e. transaction ID) character varying(36). Which we are creating on each partition.
- Our use case is limited to simple selects (we don't join with the other tables) however, we are expecting ~70 million records inserted per day and there would be couple of updates on each records where average record size would be ~ 1.5 KB.
- Currently we are thinking to have Daily partitions and as we need to keep 6 months of data thus 180 Partitions.However we have liberty to reduce the number of partitions to weekly/fortnightly/monthly, If we get comparable performance.
- We need to look current partition and previous partition for all of our use-cases/queries.
Can you please suggest what sort of combinations/partition strategy we can test considering data-volume/vacuum etc. Also let me know if some of the pg_settings can help us tuning this (I have attached my pg_settings).
Thanks and Regards,
Ravi Garg,
Mob : +91-98930-66610
On Sunday, 23 February, 2020, 03:42:13 pm IST, Justin Pryzby <pryzby@telsasoft.com> wrote:
On Sun, Feb 23, 2020 at 09:56:30AM +0000, Ravi Garg wrote:
> Hi,
> I am looking to Range Partition one of my table (i.e. TransactionLog) in PostgreSQL 11.While evaluating query performance difference between the un-partitioned and partitioned table I am getting huge difference in planning time. Planning time is very high on partitioned table.Similarly when I query by specifying partition name directly in query the planning time is much less **0.081 ms** as compared to when I query based on partition table (parent table) name in query, where planning time **6.231 ms** (Samples below).<br>
That's probably to be expected under pg11:
https://www.postgresql.org/docs/11/ddl-partitioning.html
|Too many partitions can mean longer query planning times...
|It is also important to consider the overhead of partitioning during query planning and execution. The query planner is generally able to handle partition hierarchies with up to a few hundred partitions fairly well, provided that typical queries allow the query planner to prune all but a small number of partitions. Planning times become longer and memory consumption becomes higher as more partitions are added
> There are around ~200 child partitions. Partition pruning enabled.PostgreSQL Version: PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
How large are the partitions and how many indexes each, and how large are they?
Each partition will be stat()ed and each index will be open()ed and read() for
every query. This was resolved in pg12:
https://commitfest.postgresql.org/21/1778/
--
Justin
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2020-02-24 19:44 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-02-23 09:56 PostgreSQL 11 higher Planning time on Partitioned table Ravi Garg <ravi.garg@yahoo.com>
2020-02-23 10:12 ` Justin Pryzby <pryzby@telsasoft.com>
2020-02-23 10:46 ` Justin Pryzby <pryzby@telsasoft.com>
2020-02-23 10:57 ` Ravi Garg <ravi.garg@yahoo.com>
2020-02-23 15:10 ` Justin Pryzby <pryzby@telsasoft.com>
2020-02-24 19:40 ` Ravi Garg <ravi.garg@yahoo.com>
2020-02-23 16:18 ` Imre Samu <pella.samu@gmail.com>
2020-02-24 19:44 ` Ravi Garg <ravi.garg@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox