On Mon, 07 Oct 2002 15:07:29 +0530, "Shridhar Daithankar"
<[EMAIL PROTECTED]> wrote:
>Only worry is database size. Postgresql is 111GB v/s 87 GB for mysql.
Shridhar,
here is an implementation of a set of user types: char3, char4,
char10. Put the attached files into a new directory contrib/fixchar,
make, make install, and run fixchar.sql through psql. Then create
your table as
CREATE TABLE tbl (
type int,
esn char10,
min char10,
datetime timestamp,
opc0 char3,
...
rest char4,
field0 int,
field1 char4,
...
)
This should save 76 bytes per heap tuple and 12 bytes per index tuple,
giving a database size of ~ 76 GB. I'd be very interested how this
affects performance.
Code has been tested for v7.2, it crashes on v7.3 beta 1. If this is
a problem, let me know.
Servus
Manfred
-- User type charNN: space saving replacement for char(NN)
CREATE FUNCTION charNN_in(opaque)
RETURNS charNN
AS '$libdir/fixchar'
LANGUAGE 'c';
CREATE FUNCTION charNN_out(opaque)
RETURNS opaque
AS '$libdir/fixchar'
LANGUAGE 'c';
CREATE TYPE charNN (
INPUT = charNN_in,
OUTPUT = charNN_out,
INTERNALLENGTH = NN,
ALIGNMENT = char
);
-- Operators
CREATE FUNCTION charNN_lt(charNN, charNN)
RETURNS boolean
AS '$libdir/fixchar'
LANGUAGE 'c';
CREATE FUNCTION charNN_le(charNN, charNN)
RETURNS boolean
AS '$libdir/fixchar'
LANGUAGE 'c';
CREATE FUNCTION charNN_eq(charNN, charNN)
RETURNS boolean
AS '$libdir/fixchar'
LANGUAGE 'c';
CREATE FUNCTION charNN_ge(charNN, charNN)
RETURNS boolean
AS '$libdir/fixchar'
LANGUAGE 'c';
CREATE FUNCTION charNN_gt(charNN, charNN)
RETURNS boolean
AS '$libdir/fixchar'
LANGUAGE 'c';
CREATE FUNCTION charNN_ne(charNN, charNN)
RETURNS boolean
AS '$libdir/fixchar'
LANGUAGE 'c';
CREATE OPERATOR < ( LEFTARG = charNN, RIGHTARG = charNN,
PROCEDURE = charNN_lt,
COMMUTATOR = >,
NEGATOR = >=,
RESTRICT = scalarltsel, JOIN = scalarltjoinsel
);
CREATE OPERATOR <= ( LEFTARG = charNN, RIGHTARG = charNN,
PROCEDURE = charNN_le,
COMMUTATOR = >=,
NEGATOR = >,
RESTRICT = scalarltsel, JOIN = scalarltjoinsel
);
CREATE OPERATOR = ( LEFTARG = charNN, RIGHTARG = charNN,
PROCEDURE = charNN_eq,
COMMUTATOR = =,
NEGATOR = !=,
RESTRICT = eqsel, JOIN = eqjoinsel
);
CREATE OPERATOR >= ( LEFTARG = charNN, RIGHTARG = charNN,
PROCEDURE = charNN_ge,
COMMUTATOR = <=,
NEGATOR = <,
RESTRICT = scalargtsel, JOIN = scalargtjoinsel
);
CREATE OPERATOR > ( LEFTARG = charNN, RIGHTARG = charNN,
PROCEDURE = charNN_gt,
COMMUTATOR = <,
NEGATOR = <=,
RESTRICT = scalargtsel, JOIN = scalargtjoinsel
);
CREATE OPERATOR != ( LEFTARG = charNN, RIGHTARG = charNN,
PROCEDURE = charNN_ne,
COMMUTATOR = !=,
NEGATOR = =,
RESTRICT = neqsel, JOIN = neqjoinsel
);
-- btree opclass
INSERT INTO pg_opclass (opcamid, opcname, opcintype, opcdefault, opckeytype)
SELECT a.oid, 'charNN_ops', t.oid, true, 0
FROM pg_am a, pg_type t
WHERE a.amname = 'btree'
AND t.typname = 'charNN';
--btree strategies
CREATE TEMP TABLE charNN_strat (
strat smallint,
opr name
);
INSERT INTO charNN_strat VALUES (1, '<');
INSERT INTO charNN_strat VALUES (2, '<=');
INSERT INTO charNN_strat VALUES (3, '=');
INSERT INTO charNN_strat VALUES (4, '>=');
INSERT INTO charNN_strat VALUES (5, '>');
SELECT o.oid AS opoid, s.strat
INTO TEMP TABLE charNN_ops_tmp
FROM pg_operator o, pg_type t, charNN_strat s
WHERE t.typname = 'charNN'
AND o.oprleft = t.oid
AND o.oprright = t.oid
AND o.oprname = s.opr;
INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
SELECT oc.oid, c.strat, false, c.opoid
FROM pg_opclass oc, charNN_ops_tmp c, pg_am a
WHERE opcamid = a.oid
AND opcname = 'charNN_ops'
AND a.amname = 'btree';
-- support routine
CREATE FUNCTION charNN_cmp(charNN, charNN)
RETURNS integer
AS '$libdir/fixchar'
LANGUAGE 'c';
INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
SELECT oc.oid, 1, p.oid
FROM pg_opclass oc, pg_proc p, pg_am a
WHERE a.amname = 'btree'
AND oc.opcamid = a.oid
AND oc.opcname = 'charNN_ops'
AND p.proname = 'charNN_cmp';
# fixchar Makefile
#
# Experimental code! Use at your own risk!
# 2002-10-08 mk
subdir = contrib/fixchar
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
MODULE_big = fixchar
OBJS = char3.o char4.o char10.o
DATA_built = fixchar.sql
DOCS = README.fixchar
char%.c: fixcharNN.c.in
sed -e 's/NN/$*/g' $< >$@
fixchar.sql: char3.sql char4.sql char10.sql
cat $^ >$@
char%.sql: fixcharNN.sql.in
sed -e 's/NN/$*/g' $< >$@
include $(top_srcdir)/contrib/contrib-global.mk
#include "postgres.h"
char *charNN_in(char *cstr);
char *charNN_out(char *sstr);
bool charNN_lt(char *a, char *b);
bool charNN_le(char *a, char *b);
bool charNN_eq(char *a, char *b);
bool charNN_ge(char *a, char *b);
bool charNN_gt(char *a, char *b);
bool charNN_ne(char *a, char *b);
int4 charNN_cmp(char *a, char *b);
char *
charNN_in(char *cstr)
{
char *result;
int len;
int i;
len = strlen(cstr);
if (len > NN)
len = NN;
result = (char *) palloc(NN);
for (i = 0; i < len; ++i)
result[i] = cstr[i];
for (; i < NN; ++i)
result[i] = ' ';
return result;
}/*charNN_in*/
char *
charNN_out(char *sstr)
{
char *result;
int i;
if (sstr == NULL)
return NULL;
result = (char *) palloc(NN + 1);
for (i = 0; i < NN; ++i)
result[i] = sstr[i];
result[NN] = '\0';
return result;
}/*charNN_out*/
bool
charNN_lt(char *a, char *b)
{
return (strncmp(a, b, NN) < 0);
}/*charNN_lt*/
bool
charNN_le(char *a, char *b)
{
return (strncmp(a, b, NN) <= 0);
}/*charNN_le*/
bool
charNN_eq(char *a, char *b)
{
return (strncmp(a, b, NN) == 0);
}/*charNN_eq*/
bool
charNN_ge(char *a, char *b)
{
return (strncmp(a, b, NN) >= 0);
}/*charNN_ge*/
bool
charNN_gt(char *a, char *b)
{
return (strncmp(a, b, NN) > 0);
}/*charNN_gt*/
bool
charNN_ne(char *a, char *b)
{
return (strncmp(a, b, NN) != 0);
}/*charNN_ne*/
int4
charNN_cmp(char *a, char *b)
{
return strncmp(a, b, NN);
}/*charNN_cmp*/
fixchar
=======
User data types char3, char4 and char10 as space saving replacements
for char(3), char(4), and char(10) respectively.
This is an experimental implementation for Shridhar's high volume
performance tests. It is not expected to be usable in any real world
application. Known problems are at least:
. There are no casting functions
. This does not work with multibyte character sets
Use at your own risk!
I N S T A L L
=============
. Put these files into contrib/fixchar
. cd to contrib/fixchar
. make
. make install
. cd to the directory where PG is installed
. bin/psql yourdb
yourdb=# \i share/postgresql/contrib/fixchar.sql
T E S T
=======
CREATE TABLE short (
i INT,
c3 char3,
c4 char4,
c10 char10,
primary key (i, c3)
);
CREATE TABLE long (
i INT,
c3 char(3),
c4 char(4),
c10 char(10),
primary key (i, c3)
);
INSERT INTO short VALUES (1, 'aaa', 'aaaa', 'a');
INSERT INTO long VALUES (1, 'aaa', 'aaaa', 'a');
INSERT INTO short SELECT i+1, c3, c4, c10 FROM short;
INSERT INTO long SELECT i+1, c3, c4, c10 FROM long;
INSERT INTO short SELECT i+2, c3, c4, c10 FROM short;
INSERT INTO long SELECT i+2, c3, c4, c10 FROM long;
INSERT INTO short SELECT i+4, c3, c4, c10 FROM short;
INSERT INTO long SELECT i+4, c3, c4, c10 FROM long;
INSERT INTO short SELECT i+8, c3, c4, c10 FROM short;
INSERT INTO long SELECT i+8, c3, c4, c10 FROM long;
INSERT INTO short SELECT i+16, c3, c4, c10 FROM short;
INSERT INTO long SELECT i+16, c3, c4, c10 FROM long;
INSERT INTO short SELECT i+32, c3, c4, c10 FROM short;
INSERT INTO long SELECT i+32, c3, c4, c10 FROM long;
INSERT INTO short SELECT i+64, c3, c4, c10 FROM short;
INSERT INTO long SELECT i+64, c3, c4, c10 FROM long;
INSERT INTO short SELECT i+128, c3, c4, c10 FROM short;
INSERT INTO long SELECT i+128, c3, c4, c10 FROM long;
INSERT INTO short SELECT i+256, c3, c4, c10 FROM short;
INSERT INTO long SELECT i+256, c3, c4, c10 FROM long;
INSERT INTO short SELECT i+512, c3, c4, c10 FROM short;
INSERT INTO long SELECT i+512, c3, c4, c10 FROM long;
INSERT INTO short SELECT i+1024, c3, c4, c10 FROM short;
INSERT INTO long SELECT i+1024, c3, c4, c10 FROM long;
INSERT INTO short SELECT i+2048, c3, c4, c10 FROM short;
INSERT INTO long SELECT i+2048, c3, c4, c10 FROM long;
INSERT INTO short SELECT i+4096, c3, c4, c10 FROM short;
INSERT INTO long SELECT i+4096, c3, c4, c10 FROM long;
VACUUM VERBOSE ANALYZE short;
VACUUM VERBOSE ANALYZE long;
SELECT relname,reltuples,relpages FROM pg_class WHERE relname NOT LIKE 'pg%';
relname | reltuples | relpages
------------+-----------+----------
short_pkey | 8192 | 33
short | 8192 | 57
long_pkey | 8192 | 39
long | 8192 | 69
(4 rows)
(2002-10-08 mk)
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
