我在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真的很有效.
