[INSTRM-2039] Add some indexes for common cobra/mcs/ag/target joins Created: 24/Jul/23 Updated: 07/Oct/23 Resolved: 07/Oct/23 |
|
| Status: | Done |
| Project: | Instrument control development |
| Component/s: | spt_operational_database |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | Task | Priority: | Normal |
| Reporter: | cloomis | Assignee: | Kiyoto Yabe |
| Resolution: | Done | Votes: | 0 |
| Labels: | near-term | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Sprint: | Eng13Oct |
| Description |
|
One common set of queries is the family of select xxx from cobra_match where mcs_frame_id == yyy . I think we should add an index on mcs_frame_id. FWIW cobra_match has 36M rows and takes 4G of space. Only 2.8s now, but will clearly grow. And I'll bet that a bit of thought will find a few more of these. |
| Comments |
| Comment by cloomis [ 04/Aug/23 ] |
|
Now that the run is done, can this be added? Should be obvious pretty fast whether it helps. |
| Comment by Kiyoto Yabe [ 09/Sep/23 ] |
|
I think I'm done.
Table "public.cobra_match"
Column | Type | Collation | Nullable | Default
-----------------+---------+-----------+----------+---------
pfs_visit_id | integer | | not null |
iteration | integer | | not null |
cobra_id | integer | | not null |
mcs_frame_id | integer | | |
spot_id | integer | | |
pfi_center_x_mm | real | | |
pfi_center_y_mm | real | | |
flags | integer | | |
Indexes:
"cobra_match_pkey" PRIMARY KEY, btree (pfs_visit_id, iteration, cobra_id)
"cobra_match_mcs_frame_id_idx" btree (mcs_frame_id)
"cobra_match_pfs_visit_id_iteration_cobra_id_key" UNIQUE CONSTRAINT, btree (pfs_visit_id, iteration, cobra_id)
|
| Comment by yuki.moritani [ 21/Sep/23 ] |
|
(For recording) Chi-Hung will test how much the query process will be faster before modifying the software. |
| Comment by cloomis [ 22/Sep/23 ] |
|
The queries are now fast: a few 10s of ms. And the EXPLAIN shows a simple index search. Shouldn't require any software changes. |
| Comment by Kiyoto Yabe [ 07/Oct/23 ] |
|
Now queries are actually fast, so we close this ticket. |