#2419 db: additional index
Merged 2 years ago by tkopecek. Opened 2 years ago by tkopecek.
tkopecek/koji issue2418  into  master

@@ -0,0 +1,9 @@ 

+ -- upgrade script to migrate the Koji database schema

+ -- from version 1.22 to 1.23

+ 

+ 

+ BEGIN;

+ 

+ CREATE INDEX task_by_no_parent_state_method ON task(parent, state, method) WHERE parent IS NULL;

+ 

+ COMMIT;

file modified
+1
@@ -238,6 +238,7 @@ 

  CREATE INDEX task_by_state ON task (state);

  -- CREATE INDEX task_by_parent ON task (parent);   (unique condition creates similar index)

  CREATE INDEX task_by_host ON task (host_id);

+ CREATE INDEX task_by_no_parent_state_method ON task(parent, state, method) WHERE parent IS NULL;

  

  

  -- by package, we mean srpm

Now I have found that we've few indices which are not created by default. Question is if they are worthy for everyone, or if I should just mention them in https://docs.pagure.org/koji/database_howto/

    "task_by_method" btree (method)
    "task_by_owner" btree (owner)
    "task_by_owner_no_parent" btree (owner, parent) WHERE parent IS NULL
    "task_completion_time" btree (completion_time)
    "task_create_time" btree (create_time)

1 new commit added

  • fix version
2 years ago

Should this be 1.22 to 1.23?

updated

Apart from the cut-and-paste error with the versions, the current change looks fine for what it covers.

As far as the other indexes, I wouldn't be surprised if some of them are effectively redundant. I'm fine with adding more, but it seems like we should do a little due diligence to verify they are actually currently helpful.

Also, I wonder what indexes other Koji instances might have added themselves as optimizations.

Apart from the cut-and-paste error with the versions, the current change looks fine for what it covers.

As far as the other indexes, I wouldn't be surprised if some of them are effectively redundant. I'm fine with adding more, but it seems like we should do a little due diligence to verify they are actually currently helpful.

Also, I wonder what indexes other Koji instances might have added themselves as optimizations.

Maybe we could provide a schema_extra.sql for the optimization and other non-essentials?

For what it is worth, depending on the size of the indexes, I'd just make them by default. Smaller sites may not see any performance boost, but they also might not see any real cost. Larger sites would automatically benefit without any need for extra work.

Commit c1735ba fixes this pull-request

Pull-Request has been merged by tkopecek

2 years ago