#501 add index build(canceled, is_background, source_status, id)
Merged 10 months ago by msuchy. Opened 10 months ago by msuchy.
copr/ msuchy/copr index3  into  master

@@ -0,0 +1,21 @@ 

+ """add indexes3

+ 

+ Revision ID: code4beaf000_add_indexes3.py

+ Revises: deadbeafc0de

+ Create Date: 2019-01-16 14:43:00.000000

+ 

+ """

+ # revision identifiers, used by Alembic.

+ revision = 'code4beaf000'

+ down_revision = 'deadbeafc0de'

+ 

+ from alembic import op

+ import sqlalchemy as sa

+ 

+ 

+ def upgrade():

+     op.create_index('build_canceled_is_background_source_status_id_idx', 'build', ['canceled', 'is_background', 'source_status', 'id'], unique=False)

+ 

+ 

+ def downgrade():

+     op.drop_index('build_canceled_is_background_source_status_id_idx', table_name='build')

@@ -648,7 +648,9 @@ 

  

      __table_args__ = (db.Index('build_canceled', "canceled"),

                        db.Index('build_order', "is_background", "id"),

-                       db.Index('build_filter', "source_type", "canceled"))

+                       db.Index('build_filter', "source_type", "canceled"),

+                       db.Index('build_canceled_is_background_source_status_id_idx', 'canceled', "is_background", "source_status", "id"),

+                      )

  

      def __init__(self, *args, **kwargs):

          if kwargs.get('source_type') == helpers.BuildSourceEnum("custom"):

addressing query:
SELECT build.id AS build_id, build.pkgs AS build_pkgs, build.built_packages AS build_built_packages, build.pkg_version AS build_pkg_version, build.canceled AS build_canceled, build.repos AS build_repos, build.submitted_on AS build_submitted_on, build.result_dir AS build_result_dir, build.memory_reqs AS build_memory_reqs, build.timeout AS build_timeout, build.enable_net AS build_enable_net, build.source_type AS build_source_type, build.source_json AS build_source_json, build.fail_type AS build_fail_type, build.is_background AS build_is_background, build.source_status AS build_source_status, build.srpm_url AS build_srpm_url, build.user_id AS build_user_id, build.copr_id AS build_copr_id, build.package_id AS build_package_id, build.batch_id AS build_batch_id, build.module_id AS build_module_id, build.copr_dir_id AS build_copr_dir_id, build.scm_object_id AS build_scm_object_id, build.scm_object_type AS build_scm_object_type, build.scm_object_url AS build_scm_object_url, build.update_callback AS build_update_callback
FROM build
WHERE build.canceled = false AND build.source_status = 7 AND build.is_background = false ORDER BY build.id ASC
LIMIT 100

Before:
Planning time: 0.814 ms
Execution time: 136.642 ms
(but I even seen several seconds)
After:
Planning time: 72.315 ms
Execution time: 0.968 ms

+1 as a temporary solution, but we still should re-rework the recent builds box after the release.

Pull-Request has been merged by msuchy

10 months ago