Improve performance of PostgreSQL for large databases.
authorAlex Ingram <reimu@reimuhakurei.net>
Fri, 3 Mar 2017 05:40:23 +0000 (23:40 -0600)
committerAlex Ingram <reimu@reimuhakurei.net>
Fri, 3 Mar 2017 16:14:19 +0000 (10:14 -0600)
commitd682289cc69fac3a5776ef29059cc1f54d8d37d4
tree87787186e2496e14b8b2b7834e65a75509951ad5
parent97a9b1646bb0d6362cef14bac3a577481cc01e49
Improve performance of PostgreSQL for large databases.

This change requires a change to the database schema.

Currently, in cases where buffers exist that have not had any activity for an extended period of time, during initial backlog fetching, Quassel will have to scan backwards through all activity in all buffers until it reaches activity for the buffer in question.

This adds an additional field to the buffer table, "lastmsgid", which is the messageid of the most recent message added for that buffer. This improves performance by allowing Quassel to skip straight to where the most recent activity was, instead of scanning millions of rows (in large databases) in futility.
93 files changed:
src/core/SQL/PostgreSQL/19/select_messages.sql [deleted file]
src/core/SQL/PostgreSQL/20/delete_backlog_by_uid.sql [moved from src/core/SQL/PostgreSQL/19/delete_backlog_by_uid.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/delete_backlog_for_buffer.sql [moved from src/core/SQL/PostgreSQL/19/delete_backlog_for_buffer.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/delete_backlog_for_network.sql [moved from src/core/SQL/PostgreSQL/19/delete_backlog_for_network.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/delete_buffer_for_bufferid.sql [moved from src/core/SQL/PostgreSQL/19/delete_buffer_for_bufferid.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/delete_buffers_by_uid.sql [moved from src/core/SQL/PostgreSQL/19/delete_buffers_by_uid.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/delete_buffers_for_network.sql [moved from src/core/SQL/PostgreSQL/19/delete_buffers_for_network.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/delete_identity.sql [moved from src/core/SQL/PostgreSQL/19/delete_identity.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/delete_ircservers_for_network.sql [moved from src/core/SQL/PostgreSQL/19/delete_ircservers_for_network.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/delete_network.sql [moved from src/core/SQL/PostgreSQL/19/delete_network.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/delete_networks_by_uid.sql [moved from src/core/SQL/PostgreSQL/19/delete_networks_by_uid.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/delete_nicks.sql [moved from src/core/SQL/PostgreSQL/19/delete_nicks.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/delete_quasseluser.sql [moved from src/core/SQL/PostgreSQL/19/delete_quasseluser.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/insert_buffer.sql [moved from src/core/SQL/PostgreSQL/19/insert_buffer.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/insert_identity.sql [moved from src/core/SQL/PostgreSQL/19/insert_identity.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/insert_message.sql [moved from src/core/SQL/PostgreSQL/19/insert_message.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/insert_network.sql [moved from src/core/SQL/PostgreSQL/19/insert_network.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/insert_nick.sql [moved from src/core/SQL/PostgreSQL/19/insert_nick.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/insert_quasseluser.sql [moved from src/core/SQL/PostgreSQL/19/insert_quasseluser.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/insert_sender.sql [moved from src/core/SQL/PostgreSQL/19/insert_sender.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/insert_server.sql [moved from src/core/SQL/PostgreSQL/19/insert_server.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/insert_user_setting.sql [moved from src/core/SQL/PostgreSQL/19/insert_user_setting.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/migrate_write_backlog.sql [moved from src/core/SQL/PostgreSQL/19/migrate_write_backlog.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/migrate_write_buffer.sql [moved from src/core/SQL/PostgreSQL/19/migrate_write_buffer.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/migrate_write_identity.sql [moved from src/core/SQL/PostgreSQL/19/migrate_write_identity.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/migrate_write_identity_nick.sql [moved from src/core/SQL/PostgreSQL/19/migrate_write_identity_nick.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/migrate_write_ircserver.sql [moved from src/core/SQL/PostgreSQL/19/migrate_write_ircserver.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/migrate_write_network.sql [moved from src/core/SQL/PostgreSQL/19/migrate_write_network.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/migrate_write_quasseluser.sql [moved from src/core/SQL/PostgreSQL/19/migrate_write_quasseluser.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/migrate_write_sender.sql [moved from src/core/SQL/PostgreSQL/19/migrate_write_sender.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/migrate_write_usersetting.sql [moved from src/core/SQL/PostgreSQL/19/migrate_write_usersetting.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_authuser.sql [moved from src/core/SQL/PostgreSQL/19/select_authuser.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_bufferByName.sql [moved from src/core/SQL/PostgreSQL/19/select_bufferByName.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_bufferExists.sql [moved from src/core/SQL/PostgreSQL/19/select_bufferExists.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_buffer_by_id.sql [moved from src/core/SQL/PostgreSQL/19/select_buffer_by_id.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_buffer_lastseen_messages.sql [moved from src/core/SQL/PostgreSQL/19/select_buffer_lastseen_messages.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_buffer_markerlinemsgids.sql [moved from src/core/SQL/PostgreSQL/19/select_buffer_markerlinemsgids.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_buffers.sql [moved from src/core/SQL/PostgreSQL/19/select_buffers.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_buffers_for_network.sql [moved from src/core/SQL/PostgreSQL/19/select_buffers_for_network.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_checkidentity.sql [moved from src/core/SQL/PostgreSQL/19/select_checkidentity.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_connected_networks.sql [moved from src/core/SQL/PostgreSQL/19/select_connected_networks.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_identities.sql [moved from src/core/SQL/PostgreSQL/19/select_identities.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_internaluser.sql [moved from src/core/SQL/PostgreSQL/19/select_internaluser.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_messages.sql [new file with mode: 0644]
src/core/SQL/PostgreSQL/20/select_messagesAll.sql [moved from src/core/SQL/PostgreSQL/19/select_messagesAll.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_messagesAllNew.sql [moved from src/core/SQL/PostgreSQL/19/select_messagesAllNew.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_messagesNewerThan.sql [moved from src/core/SQL/PostgreSQL/19/select_messagesNewerThan.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_messagesRange.sql [moved from src/core/SQL/PostgreSQL/19/select_messagesRange.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_networkExists.sql [moved from src/core/SQL/PostgreSQL/19/select_networkExists.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_network_awaymsg.sql [moved from src/core/SQL/PostgreSQL/19/select_network_awaymsg.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_network_usermode.sql [moved from src/core/SQL/PostgreSQL/19/select_network_usermode.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_networks_for_user.sql [moved from src/core/SQL/PostgreSQL/19/select_networks_for_user.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_nicks.sql [moved from src/core/SQL/PostgreSQL/19/select_nicks.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_persistent_channels.sql [moved from src/core/SQL/PostgreSQL/19/select_persistent_channels.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_senderid.sql [moved from src/core/SQL/PostgreSQL/19/select_senderid.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_servers_for_network.sql [moved from src/core/SQL/PostgreSQL/19/select_servers_for_network.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_user_setting.sql [moved from src/core/SQL/PostgreSQL/19/select_user_setting.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/select_userid.sql [moved from src/core/SQL/PostgreSQL/19/select_userid.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/setup_000_quasseluser.sql [moved from src/core/SQL/PostgreSQL/19/setup_000_quasseluser.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/setup_010_sender.sql [moved from src/core/SQL/PostgreSQL/19/setup_010_sender.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/setup_020_identity.sql [moved from src/core/SQL/PostgreSQL/19/setup_020_identity.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/setup_030_identity_nick.sql [moved from src/core/SQL/PostgreSQL/19/setup_030_identity_nick.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/setup_040_network.sql [moved from src/core/SQL/PostgreSQL/19/setup_040_network.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/setup_050_buffer.sql [moved from src/core/SQL/PostgreSQL/19/setup_050_buffer.sql with 92% similarity]
src/core/SQL/PostgreSQL/20/setup_060_backlog.sql [moved from src/core/SQL/PostgreSQL/19/setup_060_backlog.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/setup_070_coreinfo.sql [moved from src/core/SQL/PostgreSQL/19/setup_070_coreinfo.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/setup_080_ircservers.sql [moved from src/core/SQL/PostgreSQL/19/setup_080_ircservers.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/setup_090_backlog_idx.sql [moved from src/core/SQL/PostgreSQL/19/setup_090_backlog_idx.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/setup_100_user_setting.sql [moved from src/core/SQL/PostgreSQL/19/setup_100_user_setting.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/setup_110_alter_sender_seq.sql [moved from src/core/SQL/PostgreSQL/19/setup_110_alter_sender_seq.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/setup_120_alter_messageid_seq.sql [moved from src/core/SQL/PostgreSQL/19/setup_120_alter_messageid_seq.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/setup_130_function_lastmsgid.sql [new file with mode: 0644]
src/core/SQL/PostgreSQL/20/update_backlog_bufferid.sql [moved from src/core/SQL/PostgreSQL/19/update_backlog_bufferid.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/update_buffer_lastmsgid.sql [new file with mode: 0644]
src/core/SQL/PostgreSQL/20/update_buffer_lastseen.sql [moved from src/core/SQL/PostgreSQL/19/update_buffer_lastseen.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/update_buffer_markerlinemsgid.sql [moved from src/core/SQL/PostgreSQL/19/update_buffer_markerlinemsgid.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/update_buffer_name.sql [moved from src/core/SQL/PostgreSQL/19/update_buffer_name.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/update_buffer_persistent_channel.sql [moved from src/core/SQL/PostgreSQL/19/update_buffer_persistent_channel.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/update_buffer_set_channel_key.sql [moved from src/core/SQL/PostgreSQL/19/update_buffer_set_channel_key.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/update_identity.sql [moved from src/core/SQL/PostgreSQL/19/update_identity.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/update_network.sql [moved from src/core/SQL/PostgreSQL/19/update_network.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/update_network_connected.sql [moved from src/core/SQL/PostgreSQL/19/update_network_connected.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/update_network_set_awaymsg.sql [moved from src/core/SQL/PostgreSQL/19/update_network_set_awaymsg.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/update_network_set_usermode.sql [moved from src/core/SQL/PostgreSQL/19/update_network_set_usermode.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/update_user_setting.sql [moved from src/core/SQL/PostgreSQL/19/update_user_setting.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/update_username.sql [moved from src/core/SQL/PostgreSQL/19/update_username.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/update_userpassword.sql [moved from src/core/SQL/PostgreSQL/19/update_userpassword.sql with 100% similarity]
src/core/SQL/PostgreSQL/20/upgrade_000_alter_buffer_add_lastmsgid.sql [new file with mode: 0644]
src/core/SQL/PostgreSQL/20/upgrade_001_add_function_populate_lastmsgid.sql [new file with mode: 0644]
src/core/SQL/PostgreSQL/20/upgrade_002_run_function_populate_lastmsgid.sql [new file with mode: 0644]
src/core/postgresqlstorage.cpp
src/core/postgresqlstorage.h
src/core/sql.qrc