Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gaYx8-0001ri-Mr for pgsql-hackers@arkaria.postgresql.org; Sat, 22 Dec 2018 04:31:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gaYx6-00059l-Nz for pgsql-hackers@arkaria.postgresql.org; Sat, 22 Dec 2018 04:31:52 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gaYx6-00059d-3C for pgsql-hackers@lists.postgresql.org; Sat, 22 Dec 2018 04:31:52 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gaYx3-0002j0-56 for pgsql-hackers@lists.postgresql.org; Sat, 22 Dec 2018 04:31:50 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id wBM4VkqG001854 for ; Fri, 21 Dec 2018 23:31:46 -0500 From: Tom Lane To: pgsql-hackers@lists.postgresql.org Subject: Re: Joins on TID In-reply-to: <17443.1545435266@sss.pgh.pa.us> References: <17443.1545435266@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Fri, 21 Dec 2018 18:34:26 -0500" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <1805.1545453037.0@sss.pgh.pa.us> Date: Fri, 21 Dec 2018 23:31:46 -0500 Message-ID: <1853.1545453106@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1805.1545453037.1@sss.pgh.pa.us> BTW, if we're to start taking joins on TID seriously, we should also add the missing hash opclass for TID, so that you can do hash joins when dealing with a lot of rows. (In principle this also enables things like hash aggregation, though I'm not very clear on a use-case for grouping by TID.) regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="hash-opclass-for-tid-1.patch"; charset="us-ascii" Content-ID: <1805.1545453037.2@sss.pgh.pa.us> Content-Description: hash-opclass-for-tid-1.patch Content-Transfer-Encoding: quoted-printable diff --git a/src/backend/utils/adt/tid.c b/src/backend/utils/adt/tid.c index 41d540b..7b25947 100644 *** a/src/backend/utils/adt/tid.c --- b/src/backend/utils/adt/tid.c *************** *** 20,25 **** --- 20,26 ---- #include #include = + #include "access/hash.h" #include "access/heapam.h" #include "access/sysattr.h" #include "catalog/namespace.h" *************** tidsmaller(PG_FUNCTION_ARGS) *** 239,244 **** --- 240,272 ---- PG_RETURN_ITEMPOINTER(ItemPointerCompare(arg1, arg2) <=3D 0 ? arg1 : ar= g2); } = + Datum + hashtid(PG_FUNCTION_ARGS) + { + ItemPointer key =3D PG_GETARG_ITEMPOINTER(0); + = + /* + * While you'll probably have a lot of trouble with a compiler that + * insists on appending pad space to struct ItemPointerData, we can at + * least make this code work, by not using sizeof(ItemPointerData). + * Instead rely on knowing the sizes of the component fields. + */ + return hash_any((unsigned char *) key, + sizeof(BlockIdData) + sizeof(OffsetNumber)); + } + = + Datum + hashtidextended(PG_FUNCTION_ARGS) + { + ItemPointer key =3D PG_GETARG_ITEMPOINTER(0); + uint64 seed =3D PG_GETARG_INT64(1); + = + /* As above */ + return hash_any_extended((unsigned char *) key, + sizeof(BlockIdData) + sizeof(OffsetNumber), + seed); + } + = = /* * Functions to get latest tid of a specified tuple. diff --git a/src/include/catalog/pg_amop.dat b/src/include/catalog/pg_amop= .dat index e689c9b..436f1bd 100644 *** a/src/include/catalog/pg_amop.dat --- b/src/include/catalog/pg_amop.dat *************** *** 1013,1018 **** --- 1013,1022 ---- { amopfamily =3D> 'hash/cid_ops', amoplefttype =3D> 'cid', amoprighttype= =3D> 'cid', amopstrategy =3D> '1', amopopr =3D> '=3D(cid,cid)', amopmethod =3D> 'h= ash' }, = + # tid_ops + { amopfamily =3D> 'hash/tid_ops', amoplefttype =3D> 'tid', amoprighttype= =3D> 'tid', + amopstrategy =3D> '1', amopopr =3D> '=3D(tid,tid)', amopmethod =3D> 'h= ash' }, + = # text_pattern_ops { amopfamily =3D> 'hash/text_pattern_ops', amoplefttype =3D> 'text', amoprighttype =3D> 'text', amopstrategy =3D> '1', amopopr =3D> '=3D(te= xt,text)', diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_am= proc.dat index bbcee26..8ddb699 100644 *** a/src/include/catalog/pg_amproc.dat --- b/src/include/catalog/pg_amproc.dat *************** *** 340,345 **** --- 340,349 ---- amprocrighttype =3D> 'cid', amprocnum =3D> '1', amproc =3D> 'hashint4'= }, { amprocfamily =3D> 'hash/cid_ops', amproclefttype =3D> 'cid', amprocrighttype =3D> 'cid', amprocnum =3D> '2', amproc =3D> 'hashint4e= xtended' }, + { amprocfamily =3D> 'hash/tid_ops', amproclefttype =3D> 'tid', + amprocrighttype =3D> 'tid', amprocnum =3D> '1', amproc =3D> 'hashtid' = }, + { amprocfamily =3D> 'hash/tid_ops', amproclefttype =3D> 'tid', + amprocrighttype =3D> 'tid', amprocnum =3D> '2', amproc =3D> 'hashtidex= tended' }, { amprocfamily =3D> 'hash/text_pattern_ops', amproclefttype =3D> 'text', amprocrighttype =3D> 'text', amprocnum =3D> '1', amproc =3D> 'hashtext= ' }, { amprocfamily =3D> 'hash/text_pattern_ops', amproclefttype =3D> 'text', diff --git a/src/include/catalog/pg_opclass.dat b/src/include/catalog/pg_o= pclass.dat index 5178d04..c451d36 100644 *** a/src/include/catalog/pg_opclass.dat --- b/src/include/catalog/pg_opclass.dat *************** *** 167,172 **** --- 167,174 ---- opcintype =3D> 'xid' }, { opcmethod =3D> 'hash', opcname =3D> 'cid_ops', opcfamily =3D> 'hash/ci= d_ops', opcintype =3D> 'cid' }, + { opcmethod =3D> 'hash', opcname =3D> 'tid_ops', opcfamily =3D> 'hash/ti= d_ops', + opcintype =3D> 'tid' }, { opcmethod =3D> 'hash', opcname =3D> 'text_pattern_ops', opcfamily =3D> 'hash/text_pattern_ops', opcintype =3D> 'text', opcdefault =3D> 'f' }, diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_= operator.dat index 2abd531..e8452e1 100644 *** a/src/include/catalog/pg_operator.dat --- b/src/include/catalog/pg_operator.dat *************** *** 204,212 **** oprrest =3D> 'eqsel', oprjoin =3D> 'eqjoinsel' }, = { oid =3D> '387', oid_symbol =3D> 'TIDEqualOperator', descr =3D> 'equal'= , ! oprname =3D> '=3D', oprcanmerge =3D> 't', oprleft =3D> 'tid', oprright= =3D> 'tid', ! oprresult =3D> 'bool', oprcom =3D> '=3D(tid,tid)', oprnegate =3D> '<>(= tid,tid)', ! oprcode =3D> 'tideq', oprrest =3D> 'eqsel', oprjoin =3D> 'eqjoinsel' }= , { oid =3D> '402', descr =3D> 'not equal', oprname =3D> '<>', oprleft =3D> 'tid', oprright =3D> 'tid', oprresult = =3D> 'bool', oprcom =3D> '<>(tid,tid)', oprnegate =3D> '=3D(tid,tid)', oprcode =3D>= 'tidne', --- 204,213 ---- oprrest =3D> 'eqsel', oprjoin =3D> 'eqjoinsel' }, = { oid =3D> '387', oid_symbol =3D> 'TIDEqualOperator', descr =3D> 'equal'= , ! oprname =3D> '=3D', oprcanmerge =3D> 't', oprcanhash =3D> 't', oprleft= =3D> 'tid', ! oprright =3D> 'tid', oprresult =3D> 'bool', oprcom =3D> '=3D(tid,tid)'= , ! oprnegate =3D> '<>(tid,tid)', oprcode =3D> 'tideq', oprrest =3D> 'eqse= l', ! oprjoin =3D> 'eqjoinsel' }, { oid =3D> '402', descr =3D> 'not equal', oprname =3D> '<>', oprleft =3D> 'tid', oprright =3D> 'tid', oprresult = =3D> 'bool', oprcom =3D> '<>(tid,tid)', oprnegate =3D> '=3D(tid,tid)', oprcode =3D>= 'tidne', diff --git a/src/include/catalog/pg_opfamily.dat b/src/include/catalog/pg_= opfamily.dat index fe8a324..c5ea37b 100644 *** a/src/include/catalog/pg_opfamily.dat --- b/src/include/catalog/pg_opfamily.dat *************** *** 112,117 **** --- 112,119 ---- opfmethod =3D> 'hash', opfname =3D> 'xid_ops' }, { oid =3D> '2226', opfmethod =3D> 'hash', opfname =3D> 'cid_ops' }, + { oid =3D> '2227', + opfmethod =3D> 'hash', opfname =3D> 'tid_ops' }, { oid =3D> '2229', opfmethod =3D> 'hash', opfname =3D> 'text_pattern_ops' }, { oid =3D> '2231', diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc= .dat index acb0154..6e1e1df 100644 *** a/src/include/catalog/pg_proc.dat --- b/src/include/catalog/pg_proc.dat *************** *** 2484,2489 **** --- 2484,2495 ---- { oid =3D> '2796', descr =3D> 'smaller of two', proname =3D> 'tidsmaller', prorettype =3D> 'tid', proargtypes =3D> 'ti= d tid', prosrc =3D> 'tidsmaller' }, + { oid =3D> '2233', descr =3D> 'hash', + proname =3D> 'hashtid', prorettype =3D> 'int4', proargtypes =3D> 'tid'= , + prosrc =3D> 'hashtid' }, + { oid =3D> '2234', descr =3D> 'hash', + proname =3D> 'hashtidextended', prorettype =3D> 'int8', proargtypes =3D= > 'tid int8', + prosrc =3D> 'hashtidextended' }, = { oid =3D> '1296', proname =3D> 'timedate_pl', prolang =3D> '14', prorettype =3D> 'timest= amp', diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expe= cted/tidscan.out index 521ed1b..6a8afcb 100644 *** a/src/test/regress/expected/tidscan.out --- b/src/test/regress/expected/tidscan.out *************** EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF *** 176,179 **** --- 176,223 ---- UPDATE tidscan SET id =3D -id WHERE CURRENT OF c RETURNING *; ERROR: cursor "c" is not positioned on a row ROLLBACK; + -- bulk joins on CTID + -- (these plans don't use TID scans, but this still seems like an + -- appropriate place for these tests) + EXPLAIN (COSTS OFF) + SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid =3D t2.ctid; + QUERY PLAN = + ---------------------------------------- + Aggregate + -> Hash Join + Hash Cond: (t1.ctid =3D t2.ctid) + -> Seq Scan on tenk1 t1 + -> Hash + -> Seq Scan on tenk1 t2 + (6 rows) + = + SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid =3D t2.ctid; + count = + ------- + 10000 + (1 row) + = + SET enable_hashjoin TO off; + EXPLAIN (COSTS OFF) + SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid =3D t2.ctid; + QUERY PLAN = + ----------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.ctid =3D t2.ctid) + -> Sort + Sort Key: t1.ctid + -> Seq Scan on tenk1 t1 + -> Sort + Sort Key: t2.ctid + -> Seq Scan on tenk1 t2 + (9 rows) + = + SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid =3D t2.ctid; + count = + ------- + 10000 + (1 row) + = + RESET enable_hashjoin; DROP TABLE tidscan; diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidsc= an.sql index a8472e0..aa5c997 100644 *** a/src/test/regress/sql/tidscan.sql --- b/src/test/regress/sql/tidscan.sql *************** EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF *** 63,66 **** --- 63,78 ---- UPDATE tidscan SET id =3D -id WHERE CURRENT OF c RETURNING *; ROLLBACK; = + -- bulk joins on CTID + -- (these plans don't use TID scans, but this still seems like an + -- appropriate place for these tests) + EXPLAIN (COSTS OFF) + SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid =3D t2.ctid; + SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid =3D t2.ctid; + SET enable_hashjoin TO off; + EXPLAIN (COSTS OFF) + SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid =3D t2.ctid; + SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid =3D t2.ctid; + RESET enable_hashjoin; + = DROP TABLE tidscan; ------- =_aaaaaaaaaa0--