So I have asked these three questions:
Percentile rank that takes sorted argument (or same functionality) in PostgreSQL 9.3.5
In the first link I was working with PostgreSQL 9.3. I have now upgraded to 9.6 so I can use all of PostgreSQL's bells and whistles; however, the 9.4+ percentile rank calculation answer in the first link is taking too long. By my very rough calculations, it will take me something like 14 hours to calculate the percentile rank for every company_id I have for one given wh_calc_id. Here is the query I am working with, which does what I want but is too slow (the CTE is used to subset the data for faster testing):
with d as (
select *
from daily_data dd
where
wh_calc_id = 344
and trade_date >= (current_date - interval '20 years')
and (company_id = 858 or company_id = 524 or company_id = 726)
)
SELECT *
FROM d d1, LATERAL (
SELECT round(percent_rank(d1.daily_val) WITHIN GROUP (ORDER BY d2.daily_val)::numeric
, 6) AS pctl_calc
FROM d d2
WHERE company_id = d1.company_id
AND trade_date < d1.trade_date
) x
ORDER BY company_id, trade_date;
I see this question going two ways (probably both):
- What can I do to make the query run faster given my current data model/schema?
- What OLAP warehouse structure should I use to make this query run faster?
Note that this is just the calculation I am working on now, which is by far the slowest calculation.
The critical table, daily_data, looks like this:
CREATE TABLE public.daily_data
(
id integer NOT NULL DEFAULT nextval('daily_data_id_seq'::regclass),
company_id integer NOT NULL,
trade_date date NOT NULL,
daily_val numeric,
bbg_pulls_id integer,
gen_qtr_end_dt_id integer,
ern_release_date_id integer,
wh_calc_id integer,
dates_id bigint NOT NULL,
CONSTRAINT daily_data_pkey PRIMARY KEY (id),
CONSTRAINT daily_data_company_id_trade_date_bbg_pulls_id_key UNIQUE (company_id, trade_date, bbg_pulls_id),
CONSTRAINT daily_data_company_id_trade_date_wh_calc_id_key UNIQUE (company_id, trade_date, wh_calc_id),
CONSTRAINT daily_data_bbg_pulls_id_fkey FOREIGN KEY (bbg_pulls_id)
REFERENCES public.bbg_pulls (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT daily_data_company_id_fkey FOREIGN KEY (company_id)
REFERENCES public.company (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT daily_data_dates_id_fkey FOREIGN KEY (dates_id)
REFERENCES public.dates (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT daily_data_ern_release_date_id_fkey FOREIGN KEY (ern_release_date_id)
REFERENCES public.ern_dt (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT daily_data_gen_qtr_end_dt_id_fkey FOREIGN KEY (gen_qtr_end_dt_id)
REFERENCES public.gen_qtr_end_dt (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT daily_data_wh_calc_id_fkey FOREIGN KEY (wh_calc_id)
REFERENCES public.wh_calc (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
DEFERRABLE,
CONSTRAINT daily_data_check CHECK ((wh_calc_id IS NULL) <> (bbg_pulls_id IS NULL))
)
Here is explain analyze output from pgAdmin 3 (please tell me how to get the text out of pgAdmin 4 v1.4):
Sort (cost=1174631.94..1174648.88 rows=6774 width=40) (actual time=60330.021..60331.397 rows=14569 loops=1)
Sort Key: d1.company_id, d1.trade_date
Sort Method: quicksort Memory: 1067kB
CTE d
-> Bitmap Heap Scan on daily_data dd (cost=403.52..26448.23 rows=6774 width=42) (actual time=5.285..33.280 rows=14569oops=1)
Recheck Cond: (((wh_calc_id = 344) AND (company_id = 858) AND (trade_date >= (('now'::cstring)::date - '20ears'::interval))) OR ((wh_calc_id = 344) AND (company_id = 524) AND (trade_date >= (('now'::cstring)::date - '20 ears'::interval))) OR ( (...)
Filter: (trade_date >= (('now'::cstring)::date - '20 years'::interval))
Heap Blocks: exact=313
-> BitmapOr (cost=403.52..403.52 rows=6777 width=0) (actual time=5.150..5.150 rows=0 loops=1)
-> Bitmap Index Scan on daily_data_wh_calc_id_company_id_trade_date_idx (cost=0.00..132.81 rows=2259 width=0)actual time=1.856..1.856 rows=5026 loops=1)
Index Cond: ((wh_calc_id = 344) AND (company_id = 858) AND (trade_date >= (('now'::cstring)::date - '20ears'::interval)))
-> Bitmap Index Scan on daily_data_wh_calc_id_company_id_trade_date_idx (cost=0.00..132.81 rows=2259 width=0)actual time=1.556..1.556 rows=4517 loops=1)
Index Cond: ((wh_calc_id = 344) AND (company_id = 524) AND (trade_date >= (('now'::cstring)::date - '20ears'::interval)))
-> Bitmap Index Scan on daily_data_wh_calc_id_company_id_trade_date_idx (cost=0.00..132.81 rows=2259 width=0)actual time=1.734..1.734 rows=5026 loops=1)
Index Cond: ((wh_calc_id = 344) AND (company_id = 726) AND (trade_date >= (('now'::cstring)::date - '20ears'::interval)))
-> Nested Loop (cost=169.38..1147752.69 rows=6774 width=40) (actual time=50.164..60297.094 rows=14569 loops=1)
-> CTE Scan on d d1 (cost=0.00..135.48 rows=6774 width=40) (actual time=5.290..12.962 rows=14569 loops=1)
-> Aggregate (cost=169.38..169.40 rows=1 width=32) (actual time=4.135..4.135 rows=1 loops=14569)
-> CTE Scan on d d2 (cost=0.00..169.35 rows=11 width=32) (actual time=0.816..2.715 rows=2434 loops=14569)
Filter: ((trade_date < d1.trade_date) AND (company_id = d1.company_id))
Rows Removed by Filter: 12135
Planning time: 3.098 ms
Execution time: 60333.541 ms
Right now ultimate objective is to efficiently/quickly calculate a percentile on data in my daily_data table and save it in my daily_data table identified with a new wh_calc_id so I can pull it out for users and/or other calculations/analysis. But I can store this data in a different way to achieve this. I need help with either making the query faster via any means. Right now, I'm less concerned about quick user access to the data and more concerned about quick calculations, but user access speeds will play a roll in the end.
The daily_data table is very very large and growing every day. I'm thinking that I need to start thinking about a different warehouse data model to store pre-calculated and pre-aggregated data for quick access and optimized for faster queries. OLAP (daily processing) is fine, but I have to have the ability to process all of the calculations over one night. 14 hours for one calculation is way to slow.
I am open to any real solutions/ideas. Let me know what else you need, but most of what I think you need is in those links or in this question.
UPDATE:
+-------------------------------------+--------------------------------+
| name | setting |
+-------------------------------------+--------------------------------+
| allow_system_table_mods | off |
| application_name | pgAdmin 4 - CONN:8137730 |
| archive_command | (disabled) |
| archive_mode | off |
| archive_timeout | 0 |
| array_nulls | on |
| authentication_timeout | 60 |
| autovacuum | on |
| autovacuum_analyze_scale_factor | 0.1 |
| autovacuum_analyze_threshold | 50 |
| autovacuum_freeze_max_age | 200000000 |
| autovacuum_max_workers | 3 |
| autovacuum_multixact_freeze_max_age | 400000000 |
| autovacuum_naptime | 60 |
| autovacuum_vacuum_cost_delay | 20 |
| autovacuum_vacuum_cost_limit | -1 |
| autovacuum_vacuum_scale_factor | 0.2 |
| autovacuum_vacuum_threshold | 50 |
| autovacuum_work_mem | -1 |
| backend_flush_after | 0 |
| backslash_quote | safe_encoding |
| bgwriter_delay | 200 |
| bgwriter_flush_after | 64 |
| bgwriter_lru_maxpages | 100 |
| bgwriter_lru_multiplier | 2 |
| block_size | 8192 |
| bonjour | off |
| bonjour_name | |
| bytea_output | escape |
| check_function_bodies | on |
| checkpoint_completion_target | 0.5 |
| checkpoint_flush_after | 32 |
| checkpoint_timeout | 300 |
| checkpoint_warning | 30 |
| client_encoding | UNICODE |
| client_min_messages | notice |
| cluster_name | |
| commit_delay | 0 |
| commit_siblings | 5 |
| constraint_exclusion | partition |
| cpu_index_tuple_cost | 0.005 |
| cpu_operator_cost | 0.0025 |
| cpu_tuple_cost | 0.01 |
| cursor_tuple_fraction | 0.1 |
| data_checksums | off |
| DateStyle | ISO, MDY |
| db_user_namespace | off |
| deadlock_timeout | 1000 |
| debug_assertions | off |
| debug_pretty_print | on |
| debug_print_parse | off |
| debug_print_plan | off |
| debug_print_rewritten | off |
| default_statistics_target | 100 |
| default_tablespace | |
| default_text_search_config | pg_catalog.english |
| default_transaction_deferrable | off |
| default_transaction_isolation | read committed |
| default_transaction_read_only | off |
| default_with_oids | off |
| dynamic_library_path | $libdir |
| dynamic_shared_memory_type | posix |
| effective_cache_size | 524288 |
| effective_io_concurrency | 1 |
| enable_bitmapscan | on |
| enable_hashagg | on |
| enable_hashjoin | on |
| enable_indexonlyscan | on |
| enable_indexscan | on |
| enable_material | on |
| enable_mergejoin | on |
| enable_nestloop | on |
| enable_seqscan | on |
| enable_sort | on |
| enable_tidscan | on |
| escape_string_warning | on |
| event_source | PostgreSQL |
| exit_on_error | off |
| external_pid_file | |
| extra_float_digits | 0 |
| force_parallel_mode | off |
| from_collapse_limit | 8 |
| fsync | on |
| full_page_writes | on |
| geqo | on |
| geqo_effort | 5 |
| geqo_generations | 0 |
| geqo_pool_size | 0 |
| geqo_seed | 0 |
| geqo_selection_bias | 2 |
| geqo_threshold | 12 |
| gin_fuzzy_search_limit | 0 |
| gin_pending_list_limit | 4096 |
| hot_standby | off |
| hot_standby_feedback | off |
| huge_pages | try |
| idle_in_transaction_session_timeout | 0 |
| ignore_checksum_failure | off |
| ignore_system_indexes | off |
| integer_datetimes | on |
| IntervalStyle | postgres |
| join_collapse_limit | 8 |
| krb_caseins_users | off |
| lc_collate | en_US.UTF-8 |
| lc_ctype | en_US.UTF-8 |
| lc_messages | en_US.UTF-8 |
| lc_monetary | en_US.UTF-8 |
| lc_numeric | en_US.UTF-8 |
| lc_time | en_US.UTF-8 |
| listen_addresses | 192.168.1.7 |
| lo_compat_privileges | off |
| local_preload_libraries | |
| lock_timeout | 0 |
| log_autovacuum_min_duration | -1 |
| log_checkpoints | off |
| log_connections | off |
| log_destination | stderr |
| log_directory | pg_log |
| log_disconnections | off |
| log_duration | off |
| log_error_verbosity | default |
| log_executor_stats | off |
| log_file_mode | 600 |
| log_filename | postgresql-%Y-%m-%d_%H%M%S.log |
| log_hostname | off |
| log_line_prefix | %t |
| log_lock_waits | off |
| log_min_duration_statement | -1 |
| log_min_error_statement | error |
| log_min_messages | warning |
| log_parser_stats | off |
| log_planner_stats | off |
| log_replication_commands | off |
| log_rotation_age | 1440 |
| log_rotation_size | 10240 |
| log_statement | none |
| log_statement_stats | off |
| log_temp_files | -1 |
| log_timezone | Navajo |
| log_truncate_on_rotation | off |
| logging_collector | on |
| maintenance_work_mem | 65536 |
| max_connections | 100 |
| max_files_per_process | 1000 |
| max_function_args | 100 |
| max_identifier_length | 63 |
| max_index_keys | 32 |
| max_locks_per_transaction | 64 |
| max_parallel_workers_per_gather | 0 |
| max_pred_locks_per_transaction | 64 |
| max_prepared_transactions | 0 |
| max_replication_slots | 0 |
| max_stack_depth | 2048 |
| max_standby_archive_delay | 30000 |
| max_standby_streaming_delay | 30000 |
| max_wal_senders | 0 |
| max_wal_size | 64 |
| max_worker_processes | 8 |
| min_parallel_relation_size | 1024 |
| min_wal_size | 5 |
| old_snapshot_threshold | -1 |
| operator_precedence_warning | off |
| parallel_setup_cost | 1000 |
| parallel_tuple_cost | 0.1 |
| password_encryption | on |
| port | 5432 |
| post_auth_delay | 0 |
| pre_auth_delay | 0 |
| quote_all_identifiers | off |
| random_page_cost | 4 |
| replacement_sort_tuples | 150000 |
| restart_after_crash | on |
| row_security | on |
| search_path | "$user", public |
| segment_size | 131072 |
| seq_page_cost | 1 |
| server_encoding | UTF8 |
| server_version | 9.6.2 |
| server_version_num | 90602 |
| session_preload_libraries | |
| session_replication_role | origin |
| shared_buffers | 16384 |
| shared_preload_libraries | |
| sql_inheritance | on |
| ssl | off |
| ssl_ca_file | |
| ssl_cert_file | server.crt |
| ssl_ciphers | HIGH:MEDIUM:+3DES:!aNULL |
| ssl_crl_file | |
| ssl_ecdh_curve | prime256v1 |
| ssl_key_file | server.key |
| ssl_prefer_server_ciphers | on |
| standard_conforming_strings | on |
| statement_timeout | 0 |
| stats_temp_directory | pg_stat_tmp |
| superuser_reserved_connections | 3 |
| synchronize_seqscans | on |
| synchronous_commit | on |
| synchronous_standby_names | |
| syslog_facility | local0 |
| syslog_ident | postgres |
| syslog_sequence_numbers | on |
| syslog_split_messages | on |
| tcp_keepalives_count | 9 |
| tcp_keepalives_idle | 7200 |
| tcp_keepalives_interval | 75 |
| temp_buffers | 1024 |
| temp_file_limit | -1 |
| temp_tablespaces | |
| TimeZone | Navajo |
| timezone_abbreviations | Default |
| trace_notify | off |
| trace_recovery_messages | log |
| trace_sort | off |
| track_activities | on |
| track_activity_query_size | 1024 |
| track_commit_timestamp | off |
| track_counts | on |
| track_functions | none |
| track_io_timing | off |
| transaction_deferrable | off |
| transaction_isolation | read committed |
| transaction_read_only | off |
| transform_null_equals | off |
| unix_socket_directories | /tmp |
| unix_socket_group | |
| unix_socket_permissions | 777 |
| update_process_title | on |
| vacuum_cost_delay | 0 |
| vacuum_cost_limit | 200 |
| vacuum_cost_page_dirty | 20 |
| vacuum_cost_page_hit | 1 |
| vacuum_cost_page_miss | 10 |
| vacuum_defer_cleanup_age | 0 |
| vacuum_freeze_min_age | 50000000 |
| vacuum_freeze_table_age | 150000000 |
| vacuum_multixact_freeze_min_age | 5000000 |
| vacuum_multixact_freeze_table_age | 150000000 |
| wal_block_size | 8192 |
| wal_buffers | 512 |
| wal_compression | off |
| wal_keep_segments | 0 |
| wal_level | minimal |
| wal_log_hints | off |
| wal_receiver_status_interval | 10 |
| wal_receiver_timeout | 60000 |
| wal_retrieve_retry_interval | 5000 |
| wal_segment_size | 2048 |
| wal_sender_timeout | 60000 |
| wal_sync_method | fdatasync |
| wal_writer_delay | 200 |
| wal_writer_flush_after | 128 |
| work_mem | 4096 |
| xmlbinary | base64 |
| xmloption | content |
| zero_damaged_pages | off |
+-------------------------------------+--------------------------------+
server: it is a virtual machine running SUSE Linux Enterprise 12 64-bit with Memory: 6144 MB, datastore/disk 1.79 TB with 405 GB free.
UPDATE 2:
I took mem_work up to 8000, and shared_buffers to 64000 and it had no material effect on the query in my OQ.
alter system set work_mem = 8000
alter system set shared_buffers = 64000
Best Answer
Try raising the values for
shared_buffersandwork_mem. Don't go too crazy forwork_membecause it gets multiplied very quickly. See here for more advice:https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server