In continuation of support for Redshift
series, now Describe Table (for e.g. \d tbl) shows SORTKEY details. This resolves
Issue #6 and shows both COMPOUND / INTERLEAVED variations along with all the column names.
This change was complicated because Redshift doesn't natively support LISTAGG() function on System / Catalog tables, which meant that I had to resort to a pretty verbose
workaround. This in-turn meant that this patch shows only the first ten COMPOUND SORTKEYs of a table. Seriously speaking, it would really take an extreme corner-case, for someone to genuinely require a SORTKEY with 10+ columns.
This is not a limitation for INTERLEAVED SORTKEY since it only supports a maximum of 8 Columns.
db=# CREATE TABLE tbl_pk(custkey SMALLINT PRIMARY KEY);
CREATE TABLE
db=# \d tbl_pk
Table "public.tbl_pk"
Column | Type | Encoding | DistKey | SortKey | Preload | Encryption | Collation | Nullable | Default
---------+----------+----------+---------+---------+---------+------------+-----------+----------+---------
custkey | smallint | lzo | f | 0 | f | none | | not null |
Indexes:
PRIMARY KEY, btree (custkey)
db=# CREATE TABLE tbl_compound(
db(# custkey SMALLINT ENCODE delta NOT NULL,
db(# custname INTEGER DEFAULT 10 ENCODE raw NULL,
db(# gender BOOLEAN ENCODE RAW,
db(# address CHAR(5) ENCODE LZO,
db(# city BIGINT identity(0, 1) ENCODE DELTA,
db(# state DOUBLE PRECISION ENCODE Runlength,
db(# zipcode REAL,
db(# tempdel1 DECIMAL ENCODE Mostly16,
db(# tempdel2 BIGINT ENCODE Mostly32,
db(# tempdel3 DATE ENCODE DELTA32k,
db(# tempdel4 TIMESTAMP ENCODE Runlength,
db(# tempdel5 TIMESTAMPTZ ENCODE DELTA,
db(# tempdel6 VARCHAR(MAX) ENCODE text32k,
db(# start_date VARCHAR(10) ENCODE TEXT255
db(# )
db-# DISTSTYLE KEY
db-# DISTKEY (custname)
db-# COMPOUND SORTKEY (custkey, custname, gender, address, city, state, zipcode, tempdel1, tempdel2, tempdel3, tempdel4, tempdel5, start_date);
CREATE TABLE
db=#
db=# \d tbl_compound
Table "public.tbl_compound"
Column | Type | Encoding | DistKey | SortKey | Preload | Encryption | Collation | Nullable | Default
------------+-----------------------------+-----------+---------+---------+---------+------------+-----------+----------+------------------------------------
custkey | smallint | delta | f | 1 | f | none | | not null |
custname | integer | none | t | 2 | f | none | | | 10
gender | boolean | none | f | 3 | f | none | | |
address | character(5) | lzo | f | 4 | f | none | | |
city | bigint | delta | f | 5 | f | none | | | "identity"(494055, 4, '0,1'::text)
state | double precision | runlength | f | 6 | f | none | | |
zipcode | real | none | f | 7 | f | none | | |
tempdel1 | numeric(18,0) | mostly16 | f | 8 | f | none | | |
tempdel2 | bigint | mostly32 | f | 9 | f | none | | |
tempdel3 | date | delta32k | f | 10 | f | none | | |
tempdel4 | timestamp without time zone | runlength | f | 11 | f | none | | |
tempdel5 | timestamp with time zone | delta | f | 12 | f | none | | |
tempdel6 | character varying(65535) | text32k | f | 0 | f | none | | |
start_date | character varying(10) | text255 | f | 13 | f | none | | |
Indexes:
COMPOUND SORTKEY (address,tempdel2,start_date,custkey,zipcode,tempdel4,city,state,tempdel3,custname)
db=# CREATE TABLE tbl_interleaved(custkey SMALLINT) INTERLEAVED SORTKEY (custkey);
CREATE TABLE
db=# \d tbl_interleaved
Table "public.tbl_interleaved"
Column | Type | Encoding | DistKey | SortKey | Preload | Encryption | Collation | Nullable | Default
---------+----------+----------+---------+---------+---------+------------+-----------+----------+---------
custkey | smallint | none | f | 1 | f | none | | |
Indexes:
INTERLEAVED SORTKEY (custkey)
As a side-note, there is a
consideration as to whether this should be on a separate section of its own (and not under Indexes, which it clearly isn't). May be another day. Happy Redshifting :) !
Update (15th Sep 2017):
This project has now been named
PsqlForks!
