Cluster table - Mailing list pgsql-general

From Kopljan Michael
Subject Cluster table
Date
Msg-id 000001c84ad1$af17c510$ac00a8c0@discovery
Whole thread Raw
List pgsql-general

I’m have this table :

 

CREATE TABLE "ITerp"."Dok" (

  "id_dok" INTEGER DEFAULT "AutoIncGet"('Dok'::text) NOT NULL,

  "sifvrste" CHAR(3) NOT NULL,

  "id_objekat1" INTEGER DEFAULT 0,

  "id_objekat2" INTEGER DEFAULT 0,

  "id_partner" INTEGER DEFAULT 0,

  "broj" INTEGER NOT NULL,

  "ext" VARCHAR(15),

  "datum" DATE DEFAULT date(now()) NOT NULL,

  "valuta" DATE DEFAULT date(now()),

  "rabat" NUMERIC(10,2),

  "opis" VARCHAR(60),

  "napomena" VARCHAR(200),

  "sumiznos1" NUMERIC(18,2) DEFAULT 0,

  "sumporez1" NUMERIC(14,2) DEFAULT 0,

  "sumporez2" NUMERIC(14,2) DEFAULT 0,

  "sumiznos2" NUMERIC(18,2) DEFAULT 0,

  "sumiznos3" NUMERIC(18,2) DEFAULT 0,

  "id_komitent" INTEGER NOT NULL,

  "radnik" VARCHAR(40),

  "vezadok" INTEGER DEFAULT 0,

  "vpkkljuc" BOOLEAN DEFAULT false,

  "vpkiznos" NUMERIC(18,2) DEFAULT 0.00,

  "vpktrosak" NUMERIC(18,2) DEFAULT 0.00,

  "status" SMALLINT DEFAULT 0,

  "zapisnik" VARCHAR(140) DEFAULT ''::character varying,

  "fakbroj" VARCHAR(15),

  "vpktip" CHAR(1) DEFAULT ''::bpchar,

  "sumiznos4" NUMERIC(18,2) DEFAULT 0.00,

  "uplataiznos" NUMERIC(18,2) DEFAULT 0.00,

  "uplatadatum" DATE DEFAULT date(now()),

  "kpismoiznos" NUMERIC(18,2) DEFAULT 0.00,

  "kpismodatum" DATE DEFAULT date(now()),

  "datumpdv" DATE DEFAULT date(now()),

  "altvalutaiznos" NUMERIC(14,4) DEFAULT 0.00,

  "altvalutamon" VARCHAR(5) DEFAULT ''::bpchar,

  "vpkcarina" BOOLEAN DEFAULT false,

  "tipracuna" INTEGER DEFAULT 1,

  "cenaext" NUMERIC(14,4) DEFAULT 0.000,

  "pecat_i" TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT now(),

  "pecat_u" TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT now(),

  "idtoken" NUMERIC(14,4),

  "id_storno" INTEGER DEFAULT 0,

  CONSTRAINT "Dok_pkey" PRIMARY KEY("id_dok"),

  CONSTRAINT "fk_komitent" FOREIGN KEY ("id_komitent")

    REFERENCES "ITerp"."Komitent"("id_komitent")

    ON DELETE RESTRICT

    ON UPDATE RESTRICT

    NOT DEFERRABLE,

  CONSTRAINT "fk_vrsta" FOREIGN KEY ("sifvrste")

    REFERENCES "ITerp"."VrstaDok"("sifvrsta")

    ON DELETE RESTRICT

    ON UPDATE RESTRICT

    NOT DEFERRABLE

) WITHOUT OIDS;

 

 

CREATE UNIQUE INDEX "broj_idx_D" ON "ITerp"."Dok"

  USING btree ("sifvrste", "broj", "id_komitent", "id_objekat1");

 

CREATE INDEX "cluster_idx_dok" ON "ITerp"."Dok"

  USING btree ("id_komitent", "sifvrste", "datum", "broj", "id_dok");

 

CREATE INDEX "datum_idx_Dok" ON "ITerp"."Dok"

  USING btree ("datum");

 

CREATE UNIQUE INDEX "fakbroj_idx" ON "ITerp"."Dok"

  USING btree ("id_komitent", "sifvrste", (btrim((fakbroj)::text)))

  WHERE (((((sifvrste = 'OTP'::bpchar) OR (sifvrste = 'FAK'::bpchar)) OR (sifvrste = 'PFK'::bpchar)) OR (sifvrste = 'AVU'::bpchar)) OR (sifvrste = 'AVS'::bpchar));

 

CREATE INDEX "idtoken_idx" ON "ITerp"."Dok"

  USING btree ("idtoken");

 

CREATE INDEX "komitentD_idx" ON "ITerp"."Dok"

  USING btree ("id_komitent");

 

CREATE INDEX "objekat1D_idx" ON "ITerp"."Dok"

  USING btree ("id_objekat1");

 

CREATE INDEX "objekat2D_idx" ON "ITerp"."Dok"

  USING btree ("id_objekat2");

 

CREATE INDEX "partnerD_idx" ON "ITerp"."Dok"

  USING btree ("id_partner");

 

CREATE INDEX "pecat_i_idx_dok" ON "ITerp"."Dok"

  USING btree ("pecat_i");

 

CREATE INDEX "sifvrste_idx" ON "ITerp"."Dok"

  USING btree ("sifvrste");

 

CREATE INDEX "vezaD_idx" ON "ITerp"."Dok"

  USING btree ("vezadok");

 

 

Table is physically ordered by primary key. I try to cluster table with :

 

CLUSTER cluster_idx_dok" ON "ITerp"."Dok" ;

 

but Table is still physically ordered by primary key. Why table is not reordered by index?

pgsql-general by date:

Previous
From: Vincent Bernat
Date:
Subject: Re: Prefix search, word completion, full text search
Next
From: "Kopljan Michael"
Date:
Subject: Cluster table