Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

add indices for resc_id and data_is_dirty #4181

Closed
2 tasks done
trel opened this issue Nov 21, 2018 · 1 comment
Closed
2 tasks done

add indices for resc_id and data_is_dirty #4181

trel opened this issue Nov 21, 2018 · 1 comment
Assignees
Milestone

Comments

@trel
Copy link
Member

trel commented Nov 21, 2018

  • master
  • 4-2-stable

  1. implement new index for both resc_id and data_is_dirty on r_resc_main table
  2. investigate and post speed improvements for moderate-sized table (1M rows?)

Discovered when looking for out-of-date replicas and matching by resource hierarchy (now that resc_name is no longer used).

@trel trel added this to the 4.2.5 milestone Nov 21, 2018
@trel trel self-assigned this Nov 22, 2018
@trel
Copy link
Member Author

trel commented Nov 26, 2018

  • sample database - r_data_main has 309M rows
  • sample query filtering on resc_id takes 116 seconds
  • added index with create index idx_data_main7 on R_DATA_MAIN (resc_id);
  • index took 300 sec to create
  • same query ran in 16 seconds (>7x faster)

trel added a commit to trel/irods that referenced this issue Jan 27, 2019
trel added a commit to trel/irods that referenced this issue Jan 27, 2019
trel added a commit that referenced this issue Jan 27, 2019
trel added a commit that referenced this issue Jan 27, 2019
@trel trel closed this as completed Jan 27, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Development

No branches or pull requests

1 participant