Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit21dd5c8

Browse files
committed
HASH partitioning for all base types
1 parent410fb61 commit21dd5c8

File tree

8 files changed

+185
-97
lines changed

8 files changed

+185
-97
lines changed

‎expected/pg_pathman.out

Lines changed: 23 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -178,7 +178,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 2;
178178
QUERY PLAN
179179
------------------------------
180180
Append
181-
-> Seq Scan onhash_rel_2
181+
-> Seq Scan onhash_rel_1
182182
Filter: (value = 2)
183183
(3 rows)
184184

@@ -187,21 +187,21 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 2 OR value = 1;
187187
------------------------------
188188
Append
189189
-> Seq Scan on hash_rel_1
190-
Filter: (value = 1)
191-
-> Seq Scan on hash_rel_2
192190
Filter: (value = 2)
193-
(5 rows)
194-
195-
EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value BETWEEN 1 AND 2;
196-
QUERY PLAN
197-
-------------------------------------------------
198-
Append
199-
-> Seq Scan on hash_rel_1
200-
Filter: ((value >= 1) AND (value <= 2))
201191
-> Seq Scan on hash_rel_2
202-
Filter: ((value>= 1) AND (value <= 2))
192+
Filter: (value = 1)
203193
(5 rows)
204194

195+
-- Temporarily commented out
196+
-- EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value BETWEEN 1 AND 2;
197+
-- QUERY PLAN
198+
-- -------------------------------------------------
199+
-- Append
200+
-- -> Seq Scan on hash_rel_1
201+
-- Filter: ((value >= 1) AND (value <= 2))
202+
-- -> Seq Scan on hash_rel_2
203+
-- Filter: ((value >= 1) AND (value <= 2))
204+
-- (5 rows)
205205
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > 2500;
206206
QUERY PLAN
207207
-----------------------------------
@@ -298,7 +298,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 2;
298298
QUERY PLAN
299299
------------------------------
300300
Append
301-
-> Seq Scan onhash_rel_2
301+
-> Seq Scan onhash_rel_1
302302
Filter: (value = 2)
303303
(3 rows)
304304

@@ -307,9 +307,9 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value = 2 OR value = 1;
307307
------------------------------
308308
Append
309309
-> Seq Scan on hash_rel_1
310-
Filter: (value = 1)
311-
-> Seq Scan on hash_rel_2
312310
Filter: (value = 2)
311+
-> Seq Scan on hash_rel_2
312+
Filter: (value = 1)
313313
(5 rows)
314314

315315
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id > 2500;
@@ -569,7 +569,7 @@ SELECT * FROM ttt;
569569
CTE Scan on ttt
570570
CTE ttt
571571
-> Append
572-
-> Seq Scan onhash_rel_2
572+
-> Seq Scan onhash_rel_1
573573
Filter: (value = 2)
574574
(5 rows)
575575

@@ -1183,8 +1183,8 @@ SELECT * FROM test."TeSt";
11831183
a | b
11841184
---+---
11851185
3 | 3
1186-
1 | 1
11871186
2 | 2
1187+
1 | 1
11881188
(3 rows)
11891189

11901190
SELECT pathman.create_hash_update_trigger('test."TeSt"');
@@ -1198,31 +1198,31 @@ SELECT * FROM test."TeSt";
11981198
a | b
11991199
---+---
12001200
1 | 3
1201-
1 | 1
12021201
1 | 2
1202+
1 | 1
12031203
(3 rows)
12041204

12051205
SELECT * FROM test."TeSt" WHERE a = 1;
12061206
a | b
12071207
---+---
12081208
1 | 3
1209-
1 | 1
12101209
1 | 2
1210+
1 | 1
12111211
(3 rows)
12121212

12131213
EXPLAIN (COSTS OFF) SELECT * FROM test."TeSt" WHERE a = 1;
12141214
QUERY PLAN
12151215
----------------------------
12161216
Append
1217-
-> Seq Scan on "TeSt_1"
1217+
-> Seq Scan on "TeSt_2"
12181218
Filter: (a = 1)
12191219
(3 rows)
12201220

12211221
SELECT pathman.drop_hash_partitions('test."TeSt"');
12221222
NOTICE: drop cascades to trigger test_TeSt_insert_trigger on table test."TeSt"
12231223
NOTICE: drop cascades to 3 other objects
1224-
NOTICE:0 rows copied from test."TeSt_2"
1225-
NOTICE:3 rows copied from test."TeSt_1"
1224+
NOTICE:3 rows copied from test."TeSt_2"
1225+
NOTICE:0 rows copied from test."TeSt_1"
12261226
NOTICE: 0 rows copied from test."TeSt_0"
12271227
drop_hash_partitions
12281228
----------------------
@@ -1233,8 +1233,8 @@ SELECT * FROM test."TeSt";
12331233
a | b
12341234
---+---
12351235
1 | 3
1236-
1 | 1
12371236
1 | 2
1237+
1 | 1
12381238
(3 rows)
12391239

12401240
CREATE TABLE test."RangeRel" (

‎hash.sql

Lines changed: 39 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -18,23 +18,35 @@ CREATE OR REPLACE FUNCTION @extschema@.create_hash_partitions(
1818
) RETURNSINTEGERAS
1919
$$
2020
DECLARE
21-
v_relnameTEXT;
21+
v_relnameTEXT;
2222
v_child_relnameTEXT;
23-
v_typeTEXT;
23+
v_typeTEXT;
24+
v_plain_schemaTEXT;
25+
v_plain_relnameTEXT;
26+
v_hashfuncTEXT;
2427
BEGIN
2528
v_relname := @extschema@.validate_relname(relation);
2629
attribute :=lower(attribute);
2730
PERFORM @extschema@.common_relation_checks(relation, attribute);
2831

2932
v_type := @extschema@.get_attribute_type_name(v_relname, attribute);
30-
IF v_type::regtype!='integer'::regtype THEN
31-
RAISE EXCEPTION'Attribute type must be INTEGER';
32-
END IF;
33+
-- IF v_type::regtype != 'integer'::regtype THEN
34+
-- RAISE EXCEPTION 'Attribute type must be INTEGER';
35+
-- END IF;
36+
37+
SELECT* INTO v_plain_schema, v_plain_relname
38+
FROM @extschema@.get_plain_schema_and_relname(relation);
39+
40+
v_hashfunc := @extschema@.get_type_hash_func(v_type::regtype::oid)::regproc;
3341

3442
/* Create partitions and update pg_pathman configuration*/
3543
FOR partnumIN0..partitions_count-1
3644
LOOP
37-
v_child_relname := @extschema@.get_schema_qualified_name(relation,'.', suffix :='_'|| partnum);
45+
-- v_child_relname := @extschema@.get_schema_qualified_name(relation, '.', suffix := '_' || partnum);
46+
v_child_relname := format('%s.%s',
47+
v_plain_schema,
48+
quote_ident(v_plain_relname||'_'|| partnum));
49+
3850
EXECUTE format('CREATE TABLE %s (LIKE %s INCLUDING ALL)'
3951
, v_child_relname
4052
, v_relname);
@@ -43,8 +55,9 @@ BEGIN
4355
, v_child_relname
4456
, v_relname);
4557

46-
EXECUTE format('ALTER TABLE %s ADD CHECK (%s %% %s = %s)'
58+
EXECUTE format('ALTER TABLE %s ADD CHECK (@extschema@.get_hash(%s(%s), %s) = %s)'
4759
, v_child_relname
60+
, v_hashfunc
4861
, attribute
4962
, partitions_count
5063
, partnum);
@@ -83,7 +96,7 @@ DECLARE
8396
DECLARE
8497
hash INTEGER;
8598
BEGIN
86-
hash := NEW.%s %% %s;
99+
hash :=@extschema@.get_hash(%s(NEW.%s), %s);
87100
%s
88101
RETURN NULL;
89102
END $body$ LANGUAGE plpgsql;';
@@ -93,11 +106,11 @@ DECLARE
93106
BEFORE INSERT ON %s
94107
FOR EACH ROW EXECUTE PROCEDURE %s();';
95108
triggernameTEXT;
96-
-- fields TEXT;
97-
-- fields_format TEXT;
98109
insert_stmtTEXT;
99-
relnameTEXT;
100-
schemaTEXT;
110+
relnameTEXT;
111+
schemaTEXT;
112+
atttypeTEXT;
113+
hashfuncTEXT;
101114
BEGIN
102115
/* drop trigger and corresponding function*/
103116
PERFORM @extschema@.drop_hash_triggers(relation);
@@ -113,7 +126,11 @@ BEGIN
113126
funcname := schema||'.'|| quote_ident(format('%s_insert_trigger_func', relname));
114127
triggername := quote_ident(format('%s_%s_insert_trigger', schema, relname));
115128

116-
func := format(func, funcname, attr, partitions_count, insert_stmt);
129+
/* base hash function for type*/
130+
atttype := @extschema@.get_attribute_type_name(relation, attr);
131+
hashfunc := @extschema@.get_type_hash_func(atttype::regtype::oid)::regproc;
132+
133+
func := format(func, funcname, hashfunc, attr, partitions_count, insert_stmt);
117134
trigger := format(trigger, triggername, relation, funcname);
118135
EXECUTE func;
119136
EXECUTE trigger;
@@ -197,8 +214,8 @@ DECLARE
197214
$body$
198215
DECLARE old_hash INTEGER; new_hash INTEGER; q TEXT;
199216
BEGIN
200-
old_hash := OLD.%2$s %% %3$s;
201-
new_hash := NEW.%2$s %% %3$s;
217+
old_hash :=@extschema@.get_hash(%9$s(OLD.%2$s), %3$s);
218+
new_hash :=@extschema@.get_hash(%9$s(NEW.%2$s), %3$s);
202219
IF old_hash = new_hash THEN RETURN NEW; END IF;
203220
q := format(''DELETE FROM %8$s WHERE %4$s'', old_hash);
204221
EXECUTE q USING %5$s;
@@ -223,6 +240,8 @@ DECLARE
223240
funcnameTEXT;
224241
triggernameTEXT;
225242
child_relname_formatTEXT;
243+
atttypeTEXT;
244+
hashfuncTEXT;
226245
BEGIN
227246
relation := @extschema@.validate_relname(relation);
228247

@@ -252,9 +271,13 @@ BEGIN
252271
child_relname_format := plain_schema||'.'|| quote_ident(plain_relname||'_%s');
253272
triggername := quote_ident(format('%s_%s_update_trigger', plain_schema, plain_relname));
254273

274+
/* base hash function for type*/
275+
atttype := @extschema@.get_attribute_type_name(relation, attr);
276+
hashfunc := @extschema@.get_type_hash_func(atttype::regtype::oid)::regproc;
277+
255278
/* Format function definition and execute it*/
256279
func := format(func, funcname, attr, partitions_count, att_val_fmt,
257-
old_fields, att_fmt, new_fields, child_relname_format);
280+
old_fields, att_fmt, new_fields, child_relname_format, hashfunc);
258281
EXECUTE func;
259282

260283
/* Create triggers on child relations*/

‎init.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -340,3 +340,15 @@ RETURNS VOID AS 'pg_pathman', 'acquire_partitions_lock' LANGUAGE C STRICT;
340340
*/
341341
CREATEOR REPLACE FUNCTION @extschema@.release_partitions_lock()
342342
RETURNS VOIDAS'pg_pathman','release_partitions_lock' LANGUAGE C STRICT;
343+
344+
/*
345+
* Returns hash function OID for specified type
346+
*/
347+
CREATEOR REPLACE FUNCTION @extschema@.get_type_hash_func(OID)
348+
RETURNSOIDAS'pg_pathman','get_type_hash_func' LANGUAGE C STRICT;
349+
350+
/*
351+
* Calculates hash for integer value
352+
*/
353+
CREATEOR REPLACE FUNCTION @extschema@.get_hash(INTEGER,INTEGER)
354+
RETURNSINTEGERAS'pg_pathman','get_hash' LANGUAGE C STRICT;

‎sql/pg_pathman.sql

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -69,7 +69,16 @@ SET enable_seqscan = ON;
6969
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_rel;
7070
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE value=2;
7171
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE value=2OR value=1;
72-
EXPLAIN (COSTS OFF)SELECT*FROMtest.hash_relWHERE value BETWEEN1AND2;
72+
-- Temporarily commented out
73+
-- EXPLAIN (COSTS OFF) SELECT * FROM test.hash_rel WHERE value BETWEEN 1 AND 2;
74+
-- QUERY PLAN
75+
-- -------------------------------------------------
76+
-- Append
77+
-- -> Seq Scan on hash_rel_1
78+
-- Filter: ((value >= 1) AND (value <= 2))
79+
-- -> Seq Scan on hash_rel_2
80+
-- Filter: ((value >= 1) AND (value <= 2))
81+
-- (5 rows)
7382
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id>2500;
7483
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id>=1000AND id<3000;
7584
EXPLAIN (COSTS OFF)SELECT*FROMtest.num_range_relWHERE id>=1500AND id<2500;

‎src/init.c

Lines changed: 34 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -173,6 +173,7 @@ load_relations_hashtable(bool reinitialize)
173173
List*part_oids=NIL;
174174
ListCell*lc;
175175
char*schema;
176+
TypeCacheEntry*tce;
176177
PartRelationInfo*prel;
177178
charsql[]="SELECT pg_class.oid, pg_attribute.attnum, cfg.parttype, pg_attribute.atttypid "
178179
"FROM %s.pathman_config as cfg "
@@ -216,6 +217,10 @@ load_relations_hashtable(bool reinitialize)
216217
prel->parttype=DatumGetInt32(SPI_getbinval(tuple,tupdesc,3,&isnull));
217218
prel->atttype=DatumGetObjectId(SPI_getbinval(tuple,tupdesc,4,&isnull));
218219

220+
tce=lookup_type_cache(prel->atttype,TYPECACHE_CMP_PROC |TYPECACHE_HASH_PROC);
221+
prel->cmp_proc=tce->cmp_proc;
222+
prel->hash_proc=tce->hash_proc;
223+
219224
part_oids=lappend_int(part_oids,oid);
220225
}
221226
}
@@ -507,38 +512,50 @@ read_opexpr_const(OpExpr *opexpr, int varattno, Datum *val)
507512
staticbool
508513
validate_hash_constraint(Expr*expr,PartRelationInfo*prel,int*hash)
509514
{
510-
OpExpr*eqexpr;
511-
OpExpr*modexpr;
515+
OpExpr*eqexpr;
512516
TypeCacheEntry*tce;
517+
FuncExpr*gethashfunc;
518+
FuncExpr*funcexpr;
519+
Var*var;
513520

514521
if (!IsA(expr,OpExpr))
515522
return false;
516523
eqexpr= (OpExpr*)expr;
517524

518-
/* Is this an equality operator? */
519-
tce=lookup_type_cache(prel->atttype,TYPECACHE_BTREE_OPFAMILY);
520-
if (get_op_opfamily_strategy(eqexpr->opno,tce->btree_opf)!=BTEqualStrategyNumber)
521-
return false;
522-
523-
if (!IsA(linitial(eqexpr->args),OpExpr))
525+
/*
526+
* We expect get_hash() function on the left
527+
* TODO: check that it is really the 'get_hash' function
528+
*/
529+
if (!IsA(linitial(eqexpr->args),FuncExpr))
524530
return false;
531+
gethashfunc= (FuncExpr*)linitial(eqexpr->args);
525532

526-
/* Is thisa modulus operator? */
527-
modexpr=(OpExpr*)linitial(eqexpr->args);
528-
if (modexpr->opno!=530&&modexpr->opno!=439&&modexpr->opno&&modexpr->opno!=529)
533+
/* Is thisan equality operator? */
534+
tce=lookup_type_cache(gethashfunc->funcresulttype,TYPECACHE_BTREE_OPFAMILY);
535+
if (get_op_opfamily_strategy(eqexpr->opno,tce->btree_opf)!=BTEqualStrategyNumber)
529536
return false;
530537

531-
if (list_length(modexpr->args)==2)
538+
if (list_length(gethashfunc->args)==2)
532539
{
533-
Node*left=linitial(modexpr->args);
534-
Node*right=lsecond(modexpr->args);
540+
Node*first=linitial(gethashfunc->args);
541+
Node*second=lsecond(gethashfunc->args);
535542
Const*mod_result;
536543

537-
if ( !IsA(left,Var)|| !IsA(right,Const) )
544+
if ( !IsA(first,FuncExpr)|| !IsA(second,Const) )
545+
return false;
546+
547+
/* Check that function is the base hash function for the type */
548+
funcexpr= (FuncExpr*)first;
549+
if (funcexpr->funcid!=prel->hash_proc|| !IsA(linitial(funcexpr->args),Var))
538550
return false;
539-
if ( ((Var*)left)->varattno!=prel->attnum )
551+
552+
/* Check that argument is partitioning key attribute */
553+
var= (Var*)linitial(funcexpr->args);
554+
if (var->varattno!=prel->attnum)
540555
return false;
541-
if (DatumGetInt32(((Const*)right)->constvalue)!=prel->children.length)
556+
557+
/* Check that const value less than partitions count */
558+
if (DatumGetInt32(((Const*)second)->constvalue)!=prel->children.length)
542559
return false;
543560

544561
if ( !IsA(lsecond(eqexpr->args),Const) )

‎src/pathman.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -77,7 +77,8 @@ typedef struct PartRelationInfo
7777
PartTypeparttype;
7878
Indexattnum;
7979
Oidatttype;
80-
80+
Oidcmp_proc;
81+
Oidhash_proc;
8182
}PartRelationInfo;
8283

8384
/*
@@ -200,6 +201,7 @@ char *get_extension_schema(void);
200201
FmgrInfo*get_cmp_func(Oidtype1,Oidtype2);
201202
Oidcreate_partitions_bg_worker(Oidrelid,Datumvalue,Oidvalue_type,bool*crashed);
202203
Oidcreate_partitions(Oidrelid,Datumvalue,Oidvalue_type,bool*crashed);
204+
uint32make_hash(uint32value,uint32partitions);
203205

204206
/* copied from allpaths.h */
205207
voidset_append_rel_size(PlannerInfo*root,RelOptInfo*rel,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp