3

So I have asked these three questions:

Percentile rank that takes sorted argument (or same functionality) in PostgreSQL 9.3.5

https://quant.stackexchange.com/questions/29572/building-financial-data-time-series-database-from-scratch

Schema for Financial Database

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

  1. What can I do to make the query run faster given my current data model/schema?
  2. 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

bumped to the homepage by Community yesterday

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

0

Try raising the values for shared_buffers and work_mem. Don't go too crazy for work_mem because it gets multiplied very quickly. See here for more advice:

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

  • Thank you for your suggestion. I took mem_work up to 8000, and shared_buffers to 64000 and it had no material effect on the query in my OQ. – mountainclimber Apr 21 '17 at 19:31
  • 4
    Increasing work_mem won't help as all sorting is already done in memory. – a_horse_with_no_name Apr 21 '17 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.