diff options
| author | Yorhel <git@yorhel.nl> | 2021-02-24 09:46:03 +0100 |
|---|---|---|
| committer | Yorhel <git@yorhel.nl> | 2021-02-24 10:25:45 +0100 |
| commit | 530a9a65f90001678f3340dff5edc155cafec827 (patch) | |
| tree | 19ba3be50554c5b02df9aa24da0f231b4c7c2e22 | |
| parent | 30070e326f18789f8b82252090b269166d5ade22 (diff) | |
SQL/vndbid: Add HASHES & MERGES options to eq operator + equalimage btree function
The HASHES and MERGES options enable some pretty important join
strategies, amazing how I haven't stumbled upon major performance issues
before without them.
The equalimage function enables btree key deduplication in Postgres 13+.
Won't have a major impact on performance, but still a nice to have.
I don't have a migration script for these changes, it'll involve
updating all existing columns and indices that use the vndbid type. I'll
instead work on a script to dump all data and use the sql/ files to
re-import everything. Been wanting such a script for a while, anyway.
| -rw-r--r-- | README.md | 2 | ||||
| -rw-r--r-- | sql/vndbid.sql | 7 |
2 files changed, 5 insertions, 4 deletions
@@ -65,7 +65,7 @@ Global requirements: - Linux, or an OS that resembles Linux. Chances are VNDB won't run on Windows. - A standard C build system (make/gcc/etc) -- PostgreSQL 10+ (including development files) +- PostgreSQL 13+ (including development files) - Perl 5.26+ - Elm 0.19.1 - Graphviz diff --git a/sql/vndbid.sql b/sql/vndbid.sql index 85bbdeeb..4bd29de2 100644 --- a/sql/vndbid.sql +++ b/sql/vndbid.sql @@ -6,7 +6,7 @@ -- A 'vndbid' represents an identifier used on the site and is essentially a -- (type,number) tuple, e.g. 'v17', 'r102', 'sf500'. It is not strictly limited -- to database entries with an edit history, any type-prefixed integer could be --- added here. 'u' and 't' types are not yet supported but may be added later. +-- added here. -- -- Main advantage of this type is convenience and domain separation. Comparing -- vndbids of different types will always return false, so it's less prone to @@ -66,7 +66,7 @@ CREATE TYPE vndbid ( CREATE OPERATOR < (leftarg = vndbid, rightarg = vndbid, procedure = vndbid_lt, commutator = > , negator = >=, restrict = scalarltsel, join = scalarltjoinsel); CREATE OPERATOR <= (leftarg = vndbid, rightarg = vndbid, procedure = vndbid_le, commutator = >=, negator = > , restrict = scalarlesel, join = scalarlejoinsel); -CREATE OPERATOR = (leftarg = vndbid, rightarg = vndbid, procedure = vndbid_eq, commutator = = , negator = <>, restrict = eqsel, join = eqjoinsel); +CREATE OPERATOR = (leftarg = vndbid, rightarg = vndbid, procedure = vndbid_eq, commutator = = , negator = <>, restrict = eqsel, join = eqjoinsel, HASHES, MERGES); CREATE OPERATOR <> (leftarg = vndbid, rightarg = vndbid, procedure = vndbid_ne, commutator = <>, negator = =, restrict = neqsel, join = neqjoinsel); CREATE OPERATOR >= (leftarg = vndbid, rightarg = vndbid, procedure = vndbid_ge, commutator = <=, negator = < , restrict = scalargesel, join = scalargejoinsel); CREATE OPERATOR > (leftarg = vndbid, rightarg = vndbid, procedure = vndbid_gt, commutator = < , negator = <=, restrict = scalargtsel, join = scalargtjoinsel); @@ -78,7 +78,8 @@ CREATE OPERATOR CLASS vndbid_btree_ops DEFAULT FOR TYPE vndbid USING btree AS OPERATOR 4 >=, OPERATOR 5 >, FUNCTION 1 vndbid_cmp(vndbid, vndbid), - FUNCTION 2 vndbid_sortsupport(internal); + FUNCTION 2 vndbid_sortsupport(internal), + FUNCTION 4 btequalimage(oid); CREATE OPERATOR CLASS vndbid_hash_ops DEFAULT FOR TYPE vndbid USING hash AS OPERATOR 1 =, |