#3160 Drop unused indices
Closed: Fixed 12 days ago by tkopecek. Opened 8 months ago by tkopecek.

It looks that some indices are never picked by planner. We can drop them in such case.

@kevin can you run the same command in fedora's instance? If it is displaying same behaviour there?

SELECT
    indexrelid::regclass AS INDEX, relid::regclass AS TABLE 
FROM 
    pg_stat_user_indexes 
    JOIN pg_index USING (indexrelid) 
WHERE 
    idx_scan = 0 AND indisunique IS FALSE;

             index             |          table           
-------------------------------+--------------------------
 buildroot_cg_id               | buildroot
 image_listing_archives        | image_archive_listing
 image_listing_rpms            | image_listing
 imageinfo_listing_rpms        | imageinfo_listing
 imageinfo_task_id             | imageinfo
 users_usertype                | users

On prod:

           index            |         table         
----------------------------+-----------------------
 archiveinfo_filename_idx   | archiveinfo
 archiveinfo_type_idx       | archiveinfo
 image_listing_archives     | image_archive_listing
 image_listing_rpms         | image_listing
 imageinfo_listing_rpms     | imageinfo_listing
 imageinfo_task_id          | imageinfo
 build_reservations_created | build_reservations
(7 rows)

Oddly, in stg we have:

             index              |         table                                                     
--------------------------------+-----------------------                                            
 archive_components_idx         | archive_components                                                
 archiveinfo_filename_idx       | archiveinfo
 archiveinfo_type_idx           | archiveinfo
 build_reservations_created     | build_reservations                                                
 image_listing_archives         | image_archive_listing                                             
 task_by_no_parent_state_method | task
 task_method_idx                | task                                                              
 task_owner_idx                 | task
(8 rows) 

Metadata Update from @kevin:
- Custom field Size adjusted to None

8 months ago
buildroot_cg_id         
image_listing_archives 
image_listing_rpms     
imageinfo_listing_rpms 
imageinfo_task_id      
users_usertype   

I have none of these indices in my local db. Is it possible that these are all artifacts of either a) manual workarounds or b) older schemas?

We no longer have the following tables in the schema:

image_archive_listing
image_listing
imageinfo_listing
imageinfo

Ah, the sql migration script for 1.8 says:

-- The following tables are now obsolete:
--    imageinfo
--    imageinfo_listing
-- However, we cannot drop them until after we migrate the data

The migration doc instructs admins to perform some additional cleanup.
https://docs.pagure.org/koji/migrations/migrating_to_1.8/

Ah, right, this was before images were actually builds.

Metadata Update from @tkopecek:
- Issue set to the milestone: 1.30

3 months ago

At least dropping indices should be ok #3359 Anyway, I don't believe someone has these tables and old data in them.

Metadata Update from @tkopecek:
- Issue tagged with: no_qe

12 days ago

Commit 7c6cac1 relates to this ticket

Login to comment on this ticket.

Metadata
Related Pull Requests
  • #3359 Merged 12 days ago