Skip to content

Add Index to help Hashed Storage migration on big instances

Gabriel Mazetto requested to merge 194066-add-index-to-help-hashed-storage-migration-on-big-instances into master

What does this MR do?

Adds new partial index

Creation time:

CREATE INDEX CONCURRENTLY "index_on_id_partial_with_legacy_storage" ON "projects" ("id") WHERE storage_version < 2 or storage_version IS NULL
The query has been executed. Duration: 4.531 min

Here is the explain:

explain SELECT id FROM (SELECT projects.id FROM projects WHERE (projects.storage_version < 2 OR projects.storage_version IS NULL)) as projects WHERE projects.id > 7170845 ORDER BY projects.id ASC LIMIT 200
 Limit  (cost=0.27..29.43 rows=191 width=4) (actual time=0.054..1.700 rows=170 loops=1)
   Buffers: shared hit=160 read=12
   I/O Timings: read=1.135
   ->  Index Only Scan using index_on_id_partial_with_legacy_storage on public.projects  (cost=0.27..29.43 rows=191 width=4) (actual time=0.052..1.672 rows=170 loops=1)
         Index Cond: (projects.id > 7170845)
         Heap Fetches: 13
         Buffers: shared hit=160 read=12
         I/O Timings: read=1.135

Summary:

Time: 4.307 ms
  - planning: 2.541 ms
  - execution: 1.766 ms
    - I/O read: 1.135 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 160 (~1.30 MiB) from the buffer pool
  - reads: 12 (~96.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Does this MR meet the acceptance criteria?

Conformity

Closes #194066 (closed)

Edited by Gabriel Mazetto

Merge request reports