我在Postgresql 9.0.8中有一个大型对象表(15M行),我想查询它过时的字段.
我希望将查询除以数百万,以实现可扩展性和并发目的,我希望使用updated_at字段获取所有数据,日期为几天前.
我已经在一百万个ID上尝试了很多索引和查询,而且我似乎无法使用Heroku的Ronin硬件在100秒内获得性能.
我正在寻找我没有尽力提高效率的建议.
尝试#1
- EXPLAIN ANALYZE SELECT count(*) FROM objects
- WHERE (date(updated_at)) < (date(now())-7) AND id >= 5000001 AND id < 6000001;
- INDEX USED: (date(updated_at),id)
- 268578.934 ms
尝试#2
- EXPLAIN ANALYZE SELECT count(*) FROM objects
- WHERE ((date(now()) - (date(updated_at)) > 7)) AND id >= 5000001 AND id < 6000001;
- INDEX USED: primary key
- 335555.144 ms
尝试#3
- EXPLAIN ANALYZE SELECT count(*) FROM objects
- WHERE (date(updated_at)) < (date(now())-7) AND id/1000000 = 5;
- INDEX USED: (date(updated_at),(id/1000000))
- 243427.042 ms
尝试#4
- EXPLAIN ANALYZE SELECT count(*) FROM objects
- WHERE (date(updated_at)) < (date(now())-7) AND id/1000000 = 5 AND updated_at IS NOT NULL;
- INDEX USED: (date(updated_at),(id/1000000)) WHERE updated_at IS NOT NULL
- 706714.812 ms
尝试#5(对于一个月的过期数据)
- EXPLAIN ANALYZE SELECT count(*) FROM objects
- WHERE (EXTRACT(MONTH from date(updated_at)) = 8) AND id/1000000 = 5;
- INDEX USED: (EXTRACT(MONTH from date(updated_at)),(id/1000000))
- 107241.472 ms
尝试#6
- EXPLAIN ANALYZE SELECT count(*) FROM objects
- WHERE (date(updated_at)) < (date(now())-7) AND id/1000000 = 5;
- INDEX USED: ( (id/1000000 ) ASC,updated_at DESC NULLS LAST)
- 106842.395 ms
尝试#7(见：http://explain.depesz.com/s/DQP)
- EXPLAIN ANALYZE SELECT count(*) FROM objects
- WHERE id/1000000 = 5 and (date(updated_at)) < (date(now())-7);
- INDEX USED: ( (id/1000000 ) ASC,date(updated_at) DESC NULLS LAST);
- 100732.049 ms
- Second try: 87280.728 ms
尝试#8
- EXPLAIN ANALYZE SELECT count(*) FROM objects
- WHERE (date(updated_at)) < (date(now())-7) AND id/1000000 = 5 AND updated_at IS NOT NULL;
- INDEX USED: ( (id/1000000 ) ASC,date(updated_at) ASC NULLS LAST);
- 129133.022 ms
尝试#9(部分索引根据欧文的建议,见：
http://explain.depesz.com/s/p9A)
- EXPLAIN ANALYZE SELECT count(*) FROM objects
- WHERE id BETWEEN 5000000 AND 5999999 AND (date(updated_at)) < '2012-10-23'::date;
- INDEX USED: (date(updated_at) DESC NULLS LAST)
- WHERE id BETWEEN 5000000 AND 6000000 AND date(updated_at) < '2012-10-23'::date;
- 73861.047 ms
尝试#10(根据Erwin的建议,CLUSTER).
- CREATE INDEX ix_8 on objects ( (id/1000000 ) ASC,date(updated_at) DESC NULLS LAST);
- CLUSTER entities USING ix_8;
- EXPLAIN ANALYZE SELECT count(*) FROM objects
- WHERE id/1000000 = 5 and (date(updated_at)) < (date(now())-7) ;
- 4745.595 ms
- EXPLAIN ANALYZE SELECT count(*) FROM objects
- WHERE id/1000000 = 10 and (date(updated_at)) < (date(now())-7) ;
- 17573.639 ms
==>这个解决方案似乎是赢家.我必须彻底测试以验证我的应用程序中的所有反击.
数据库设置：
从pg_settings中选择name,min_val,max_val,boot_val;
- name | min_val | max_val | boot_val
- --------------------------------+-----------+--------------+-------------------
- allow_system_table_mods | | | off
- application_name | | |
- archive_command | | |
- archive_mode | | | off
- archive_timeout | 0 | 2147483647 | 0
- array_nulls | | | on
- authentication_timeout | 1 | 600 | 60
- autovacuum | | | on
- autovacuum_analyze_scale_factor | 0 | 100 | 0.1
- autovacuum_analyze_threshold | 0 | 2147483647 | 50
- autovacuum_freeze_max_age | 100000000 | 2000000000 | 200000000
- autovacuum_max_workers | 1 | 536870911 | 3
- autovacuum_naptime | 1 | 2147483 | 60
- autovacuum_vacuum_cost_delay | -1 | 100 | 20
- autovacuum_vacuum_cost_limit | -1 | 10000 | -1
- autovacuum_vacuum_scale_factor | 0 | 100 | 0.2
- autovacuum_vacuum_threshold | 0 | 2147483647 | 50
- backslash_quote | | | safe_encoding
- bgwriter_delay | 10 | 10000 | 200
- bgwriter_lru_maxpages | 0 | 1000 | 100
- bgwriter_lru_multiplier | 0 | 10 | 2
- block_size | 8192 | 8192 | 8192
- bonjour | | | off
- bonjour_name | | |
- bytea_output | | | hex
- check_function_bodies | | | on
- checkpoint_completion_target | 0 | 1 | 0.5
- checkpoint_segments | 1 | 2147483647 | 3
- checkpoint_timeout | 30 | 3600 | 300
- checkpoint_warning | 0 | 2147483647 | 30
- client_encoding | | | sql_ASCII
- client_min_messages | | | notice
- commit_delay | 0 | 100000 | 0
- commit_siblings | 1 | 1000 | 5
- constraint_exclusion | | | partition
- cpu_index_tuple_cost | 0 | 1.79769e+308 | 0.005
- cpu_operator_cost | 0 | 1.79769e+308 | 0.0025
- cpu_tuple_cost | 0 | 1.79769e+308 | 0.01
- cursor_tuple_fraction | 0 | 1 | 0.1
- custom_variable_classes | | |
- DateStyle | | | ISO,MDY
- db_user_namespace | | | off
- deadlock_timeout | 1 | 2147483 | 1000
- debug_assertions | | | off
- debug_pretty_print | | | on
- debug_print_parse | | | off
- debug_print_plan | | | off
- debug_print_rewritten | | | off
- default_statistics_target | 1 | 10000 | 100
- default_tablespace | | |
- default_text_search_config | | | pg_catalog.simple
- default_transaction_isolation | | | read committed
- default_transaction_read_only | | | off
- default_with_oids | | | off
- effective_cache_size | 1 | 2147483647 | 16384
- effective_io_concurrency | 0 | 1000 | 1
- enable_bitmapscan | | | on
- enable_hashagg | | | on
- enable_hashjoin | | | 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
- extra_float_digits | -15 | 3 | 0
- from_collapse_limit | 1 | 2147483647 | 8
- fsync | | | on
- full_page_writes | | | on
- geqo | | | on
- geqo_effort | 1 | 10 | 5
- geqo_generations | 0 | 2147483647 | 0
- geqo_pool_size | 0 | 2147483647 | 0
- geqo_seed | 0 | 1 | 0
- geqo_selection_bias | 1.5 | 2 | 2
- geqo_threshold | 2 | 2147483647 | 12
- gin_fuzzy_search_limit | 0 | 2147483647 | 0
- hot_standby | | | off
- ignore_system_indexes | | | off
- integer_datetimes | | | on
- IntervalStyle | | | postgres
- join_collapse_limit | 1 | 2147483647 | 8
- krb_caseins_users | | | off
- krb_srvname | | | postgres
- lc_collate | | | C
- lc_ctype | | | C
- lc_messages | | |
- lc_monetary | | | C
- lc_numeric | | | C
- lc_time | | | C
- listen_addresses | | | localhost
- lo_compat_privileges | | | off
- local_preload_libraries | | |
- log_autovacuum_min_duration | -1 | 2147483 | -1
- log_checkpoints | | | off
- log_connections | | | off
- log_destination | | | stderr
- log_disconnections | | | off
- log_duration | | | off
- log_error_verbosity | | | default
- log_executor_stats | | | off
- log_hostname | | | off
- log_line_prefix | | |
- log_lock_waits | | | off
- log_min_duration_statement | -1 | 2147483 | -1
- log_min_error_statement | | | error
- log_min_messages | | | warning
- log_parser_stats | | | off
- log_planner_stats | | | off
- log_rotation_age | 0 | 35791394 | 1440
- log_rotation_size | 0 | 2097151 | 10240
- log_statement | | | none
- log_statement_stats | | | off
- log_temp_files | -1 | 2147483647 | -1
- log_timezone | | | UNKNOWN
- log_truncate_on_rotation | | | off
- logging_collector | | | off
- maintenance_work_mem | 1024 | 2097151 | 16384
- max_connections | 1 | 536870911 | 100
- max_files_per_process | 25 | 2147483647 | 1000
- max_function_args | 100 | 100 | 100
- max_identifier_length | 63 | 63 | 63
- max_index_keys | 32 | 32 | 32
- max_locks_per_transaction | 10 | 2147483647 | 64
- max_prepared_transactions | 0 | 536870911 | 0
- max_stack_depth | 100 | 2097151 | 100
- max_standby_archive_delay | -1 | 2147483 | 30000
- max_standby_streaming_delay | -1 | 2147483 | 30000
- max_wal_senders | 0 | 536870911 | 0
- password_encryption | | | on
- port | 1 | 65535 | 5432
- post_auth_delay | 0 | 2147483647 | 0
- pre_auth_delay | 0 | 60 | 0
- random_page_cost | 0 | 1.79769e+308 | 4
- search_path | | | "$user",public
- segment_size | 131072 | 131072 | 131072
- seq_page_cost | 0 | 1.79769e+308 | 1
- server_encoding | | | sql_ASCII
- server_version | | | 9.0.8
- server_version_num | 90008 | 90008 | 90008
- session_replication_role | | | origin
- shared_buffers | 16 | 1073741823 | 1024
- silent_mode | | | off
- sql_inheritance | | | on
- ssl | | | off
- ssl_renegotiation_limit | 0 | 2097151 | 524288
- standard_conforming_strings | | | off
- statement_timeout | 0 | 2147483647 | 0
- superuser_reserved_connections | 0 | 536870911 | 3
- synchronize_seqscans | | | on
- synchronous_commit | | | on
- syslog_facility | | | local0
- syslog_ident | | | postgres
- tcp_keepalives_count | 0 | 2147483647 | 0
- tcp_keepalives_idle | 0 | 2147483647 | 0
- tcp_keepalives_interval | 0 | 2147483647 | 0
- temp_buffers | 100 | 1073741823 | 1024
- temp_tablespaces | | |
- TimeZone | | | UNKNOWN
- timezone_abbreviations | | | UNKNOWN
- trace_notify | | | off
- trace_recovery_messages | | | log
- trace_sort | | | off
- track_activities | | | on
- track_activity_query_size | 100 | 102400 | 1024
- track_counts | | | on
- track_functions | | | none
- transaction_isolation | | |
- transaction_read_only | | | off
- transform_null_equals | | | off
- unix_socket_group | | |
- unix_socket_permissions | 0 | 511 | 511
- update_process_title | | | on
- vacuum_cost_delay | 0 | 100 | 0
- vacuum_cost_limit | 1 | 10000 | 200
- vacuum_cost_page_dirty | 0 | 10000 | 20
- vacuum_cost_page_hit | 0 | 10000 | 1
- vacuum_cost_page_miss | 0 | 10000 | 10
- vacuum_defer_cleanup_age | 0 | 1000000 | 0
- vacuum_freeze_min_age | 0 | 1000000000 | 50000000
- vacuum_freeze_table_age | 0 | 2000000000 | 150000000
- wal_block_size | 8192 | 8192 | 8192
- wal_buffers | 4 | 2147483647 | 8
- wal_keep_segments | 0 | 2147483647 | 0
- wal_level | | | minimal
- wal_segment_size | 2048 | 2048 | 2048
- wal_sender_delay | 1 | 10000 | 200
- wal_sync_method | | | fdatasync
- wal_writer_delay | 1 | 10000 | 200
- work_mem | 64 | 2097151 | 1024
- xmlbinary | | | base64
- xmloption | | | content
- zero_damaged_pages | | | off
- (195 rows)
首先,可以吗？你写：
I want to fetch all data with the updated_at field with a date of a
few days ago.
但你的WHERE条件是：
- (date(updated_at)) < (date(now())-7)
不应该是>？
索引
为了获得最佳性能,您可以……
>分区索引
>从索引中排除不相关的行
>使用更新的谓词在非工作时间自动重新创建索引.
您的索引可能如下所示：
- CREATE INDEX objects_id_updated_at_idx (updated_at::date DESC NULLS LAST)
- WHERE id BETWEEN 0 AND 999999
- AND updated_at > '2012-10-01 0:0'::timestamp -- some minimum date
- CREATE INDEX objects_id_updated_at_idx (updated_at::date DESC NULLS LAST)
- WHERE id BETWEEN 1000000 AND 1999999
- AND updated_at > '2012-10-01 0:0'::timestamp -- some minimum date
…
第二个条件立即从索引中排除不相关的行,这应该使它更小更快 – 取决于您的实际数据分布.根据我的初步评论,我假设你想要更新的行.
该条件还会自动排除updated_at中的NULL值 – 您似乎在表中允许这些值,并且显然希望在查询中排除.指数的有用性随着时间的推移而恶化.查询始终检索最新条目.定期使用更新的WHERE子句重新创建索引.这需要在桌子上进行独占锁定,所以在非工作时间进行.还有CREATE INDEX CONCURRENTLY以最小化锁定的持续时间：
- CREATE INDEX CONCURRENTLY objects_id_up_201211_idx; -- create new idx
- DROP INDEX objects_id_up_201210_idx; -- then drop old
关于SO的相关答案：
> Postgres returns records in wrong order
为了进一步优化,您可以像我们在评论中提到的那样使用CLUSTER.但是你需要一个完整的索引.不适用于部分索引.你会暂时创建：
- CREATE INDEX objects_full_idx (id/1000000,updated_at::date DESC NULLS LAST);
完整索引的这种形式匹配上面部分索引的排序顺序.
- CLUSTER objects USING objects_full_idx;
- ANALYZE objects;
这需要一段时间,因为表是物理重写的.它实际上也是真空.它需要在桌子上进行独占的写锁定,所以在非工作时间进行 – 如果您可以负担得起的话.同样,存在一种侵入性较小的替代方案：pg_repack
然后,您可以再次删除索引.这是一次性的影响.我至少会尝试一次,看看你的查询能从中获益多少.随后的写入操作会使效果恶化.如果你看到相当大的影响,你可以在非工作时间重复这个程序.
如果您的表收到大量写入操作,则必须权衡此步骤的成本和收益.对于许多UPDATE,请考虑将FILLFACTOR设置为低于100.在CLUSTER之前执行此操作.
询问
- SELECT count(*)
- FROM objects
- WHERE id BETWEEN 0 AND 999999 -- match conditions of partial index!
- AND updated_at > '2012-10-01 0:0'::timestamp
- AND updated_at::date > (now()::date - 7)
更多
这个相关的答案提出了一种更先进的索引分区技术：
> Can spatial index help a “range – order by – limit” query
Postgresql 9.2为您提供了几项新功能.仅凭Index-only scans就可以让你值得拥有.
确保autovacuum正常运行.您报告的CLUSTER的巨大收益可能部分归因于您从CLUSTER获得的隐含的VACUUM FULL.也许这是由Heroku自动设置的,不确定.问题中的设置看起来不错.所以这可能不是问题,CLUSTER真的很有效.
