Re: [OSSTEST PATCH v2 07/41] schema: Provide indices for sg-report-flight

[George Dunlap <George.Dunlap@xxxxxxxxxx>]

> On Jul 31, 2020, at 12:37 PM, Ian Jackson <ian.jackson@xxxxxxxxxxxxx> wrote:
> 
> These indexes allow very fast lookup of "relevant" flights eg when
> trying to justify failures.
> 
> In my ad-hoc test case, these indices (along with the subsequent
> changes to sg-report-flight and Executive.pm, reduce the runtime of
> sg-report-flight from 2-3ks (unacceptably long!) to as little as
> 5-7s seconds - a speedup of about 500x.
> 
> (Getting the database snapshot may take a while first, but deploying
> this code should help with that too by reducing long-running
> transactions.  Quoted perf timings are from snapshot acquisition.)
> 
> Without these new indexes there may be a performance change from the
> query changes.  I haven't benchmarked this so I am setting the schema
> updates to be Preparatory/Needed (ie, "Schema first" as
> schema/README.updates has it), to say that the index should be created
> before the new code is deployed.
> 
> Testing: I have tested this series by creating experimental indices
> "trial_..." in the actual production instance.  (Transactional DDL was
> very helpful with this.)  I have verified with \d that schema update
> instructions in this commit generate indexes which are equivalent to
> the trial indices.
> 
> Deployment: AFter these schema updates are applied, the trial indices
> are redundant duplicates and should be deleted.
> 
> CC: George Dunlap <George.Dunlap@xxxxxxxxxx>
> Signed-off-by: Ian Jackson <ian.jackson@xxxxxxxxxxxxx>

I have no idea if building an index on a LIKE is a good idea or not, but it 
certainly seems to be useful, so:

Reviewed-by: George Dunlap <george.dunlap@xxxxxxxxxx>