634722 - Perform index audit on reports table [tracker]

Status: RESOLVED FIXED

(Socorro :: General, task)

Description

[[:jberkus] Josh Berkus]

While looking at bug 613379, I did some poking around the indexes on "reports" and I've realized that this rather central table is not indexed optimally.  Several indexes exist for things which are almost never searched on, whereas there are no good indexes for some common searches.

I would like to perform an index audit of the reports table and rejigger its indexes to comply with the searches which are actually being performed.  This should take 3-5 hours of billable time, plus some additional for deployment to production.

Approved, Laura?

Comment 1

[[:jberkus] Josh Berkus]

Attachment: csv file of unused indexes

Attached is a list of unused indexes.  This suggests that the following indexes could be dropped:

extensions_*_extension_id_extension_version_idx on all extensions partitions; utterly unused.

the index:
reports_*_unique_uuid 
on all reports partitions is a duplicate of another index on UUID, and can also be dropped.

reports_*_hangid is a bit less clear.  On the one hand, those indexes aren't much used.  On the other hand, they are quite small.  I'm inclined to leave those indexes alone for now.

Comment 2

[[:jberkus] Josh Berkus]

actually:  reports_*_unique_uuid should be retained, and the duplicate index reports_*_uuid_key, which is not unique, should be dropped.

Patches to come.

Comment 3

[[:jberkus] Josh Berkus]

Attachment: drops unnecessary extensions index

Drop indexes script

Comment 4

[[:jberkus] Josh Berkus]

Attachment: drops duplicate reports index

Comment 5

[[:jberkus] Josh Berkus]

That takes care of dropping indexes. Adding needed indexes is more complicated, and should be tackled after the all-hands.

Comment 6

[[:jberkus] Josh Berkus]

Being split into two bugs, one for removing indexes, one for adding them.

Comment 7

[Laura Thomson :laura]

mpressman, status?

Comment 8

[Matt Pressman [:mpressman]]

unnecessary and duplicate indexes have been removed

Comment 9

[Laura Thomson :laura]

Can we close this out then?  Also, can you give us a quick summary of work done? (ie what did we get rid of?)

Comment 10

[Matt Pressman [:mpressman]]

I executed the scripts that were attached.
The attachment drops unnecessary extensions index
and
the attachment drops duplicate reports index

However, it looks like there may be more that are unused after running:
select * from pg_stat_user_indexes where relname like 'reports%' and idx_scan=0 and idx_tup_read=0 and idx_tup_fetch=0;

I will remove them and add the info that was removed.

Comment 11

[Matt Pressman [:mpressman]]

alright, the indexes from the attachment csv file of unused indexes have been removed

Comment 12

[[:jberkus] Josh Berkus]

Um, you didn't remove the additional indexes you mention above, did you?

There's reasons for indexes to have a fetch count of 0 but still be necessary.

Comment 13

[[:jberkus] Josh Berkus]

Also, the scripts were already run on production for 1.7.8.  See the bug this bug is a tracker of.  This just means that mpressman running the scripts again was a no-op.

Comment 14

[[:jberkus] Josh Berkus]

Reopening since this is a *tracker* bug.

Comment 15

[Matt Pressman [:mpressman]]

Attachment: Zero scans on 6/18

Comment 16

[Sheeri Cabral [:sheeri]]

Assigning this to Laura, as the blocker bug is hers, and likely both these bugs are long done by now and can be closed.

Comment 17

[Lonnen :lonnen]

untargeting. can this be closed?

Comment 18

[Lonnen :lonnen]

ping. laura?

Comment 19

[Laura Thomson :laura]

I'm ok with this being closed.