[nova] indices on shadow-able fields

Matt Riedemann mriedemos at gmail.com
Tue Apr 9 16:00:39 UTC 2019


On 4/9/2019 10:34 AM, Sahid Orentino Ferdjaoui wrote:
> I don't think we query shadow tables or it's when we want to purge
> them, so basically that is going to be based on datetime.  I may be
> wrong but adding an index for a datetime field may not be so
> interesting since the index is going to be quite large because of the
> hours, minutes, seconds. That needs to be verified the database may
> provides feature to index datetime using date only. If so I guess you
> raised a good point, if not, indexing such fields is not going to help
> and probably going to consum more resources.

I don't think he's talking about querying the shadow tables but the 
queries made on (soft) deleted records in the normal tables to figure 
out what to populate / move into the shadow tables, e.g.:

https://github.com/openstack/nova/blob/d42a007425d9adb691134137e1e0b7dda356df62/nova/db/sqlalchemy/api.py#L5523

Which says:

- for all soft deleted instances
- find me all of their soft deleted actions
- and for those actions, soft delete all of the events for each action

There was a question in IRC yesterday if the instance_action_events 
table had an index on action_id because of this query:

https://github.com/openstack/nova/blob/d42a007425d9adb691134137e1e0b7dda356df62/nova/db/sqlalchemy/api.py#L5535

And Jay said there was because of the ForeignKeyConstraint on that 
column. There isn't an index on action_id/deleted though, but I'm not 
seeing where that is being used here.

-- 

Thanks,

Matt



More information about the openstack-discuss mailing list