Add WithNoVersionSchemaForRawSQL option
#365
Merged
Conversation
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Update the `previous_version` function to return the most recent previous version for which a version schema exists.
Add an option to control the creation of version schema for raw SQL migrations.
4c3af56 to
ebbb9a9
Compare
exekias
reviewed
Jul 1, 2024
exekias
approved these changes
Jul 1, 2024
andrew-farries
added a commit
that referenced
this pull request
Jul 4, 2024
#365 changed the definition of `previous_version` and introduced a performance regression. Update the function wit 7E5B h extra `WHERE` clauses so that the recursive CTE can take better advantage of the table PK index. On a large (~1M record) `pgroll.migrations` table the query analysis for the recursive CTE is: **Before**: ``` +----------------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | |----------------------------------------------------------------------------------------------------------------------------------------------------------| | CTE Scan on ancestors (cost=1819977.60..1819978.62 rows=51 width=214) (actual time=1163.608..6463.980 rows=1 loops=1) | | CTE ancestors | | -> Recursive Union (cost=0.00..1819977.60 rows=51 width=114) (actual time=1163.606..6463.977 rows=1 loops=1) | | -> Seq Scan on migrations (cost=0.00..442329.71 rows=1 width=114) (actual time=1163.604..6385.489 rows=1 loops=1) | | Filter: (name = latest_version('bb_00bo06t68d5ot7i5k5m8um70kg_bb19qc'::name)) | | Rows Removed by Filter: 1193140 | | -> Nested Loop (cost=0.55..137764.74 rows=5 width=114) (actual time=78.485..78.486 rows=0 loops=1) | | -> WorkTable Scan on ancestors a (cost=0.00..0.20 rows=10 width=36) (actual time=0.001..0.002 rows=1 loops=1) | | -> Index Scan using migrations_pkey on migrations m (cost=0.55..13776.44 rows=1 width=110) (actual time=78.459..78.459 rows=0 loops=1) | | Index Cond: (name = a.parent) | | Filter: ((migration_type)::text = 'inferred'::text) | | Rows Removed by Filter: 1 | | Planning Time: 0.764 ms | | JIT: | | Functions: 11 | | Options: Inlining true, Optimization true, Expressions true, Deforming true | | Timing: Generation 2.222 ms, Inlining 29.422 ms, Optimization 51.028 ms, Emission 24.552 ms, Total 107.225 ms | | Execution Time: 6466.347 ms | +----------------------------------------------------------------------------------------------------------------------------------------------------------+ ``` **After**: ``` +----------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | |----------------------------------------------------------------------------------------------------------------------------------------------------| | CTE Scan on ancestors (cost=987.99..988.21 rows=11 width=214) (actual time=0.277..0.379 rows=1 loops=1) | | CTE ancestors | | -> Recursive Union (cost=0.55..987.99 rows=11 width=114) (actual time=0.275..0.377 rows=1 loops=1) | | -> Index Scan using history_is_linear on migrations (cost=0.55..128.36 rows=1 width=114) (actual time=0.274..0.354 rows=1 loops=1) | | Index Cond: (schema = 'bb_00bo06t68d5ot7i5k5m8um70kg_bb19qc'::name) | | Filter: (name = latest_version('bb_00bo06t68d5ot7i5k5m8um70kg_bb19qc'::name)) | | Rows Removed by Filter: 5 | | -> Nested Loop (cost=0.55..85.95 rows=1 width=114) (actual time=0.020..0.020 rows=0 loops=1) | | -> WorkTable Scan on ancestors a (cost=0.00..0.20 rows=10 width=100) (actual time=0.001..0.001 rows=1 loops=1) | | -> Index Scan using migrations_pkey on migrations m (cost=0.55..8.57 rows=1 width=110) (actual time=0.017..0.017 rows=0 loops=1) | | Index Cond: ((schema = a.schema) AND (name = a.parent)) | | Filter: ((migration_type)::text = 'inferred'::text) | | Rows Removed by Filter: 1 | | Planning Time: 0.744 ms | | Execution Time: 0.459 ms | +----------------------------------------------------------------------------------------------------------------------------------------------------+ ``` ie the recursive portion of the table is able to take advantage of the table's PK (defined on `(schema, name)`).
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.
This suggestion is invalid because no changes were made to the code.
Suggestions cannot be applied while the pull request is closed.
Suggestions cannot be applied while viewing a subset of changes.
Only one suggestion per line can be applied in a batch.
Add this suggestion to a batch that can be applied as a single commit.
Applying suggestions on deleted lines is not supported.
You must change the existing code in this line in order to create a valid suggestion.
Outdated suggestions cannot be applied.
This suggestion has been applied or marked resolved.
Suggestions cannot be applied from pending reviews.
Suggestions cannot be applied on multi-line comments.
Suggestions cannot be applied while the pull request is queued to merge.
Suggestion cannot be applied right now. Please check back later.
Add a new
WithNoVersionSchemaForRawSQLoption to control whether or not version schema should be created for raw SQL migrations.With the option set, a raw SQL migration: