Cluster table

From: "Kopljan Michael" <kopljan(at)beotel(dot)net>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Cluster table
Date: 2007-12-30 10:49:41
Message-ID: 000001c84ad1$af17c510$ac00a8c0@discovery
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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?

Browse pgsql-general by date

  From Date Subject
Next Message Kopljan Michael 2007-12-30 10:49:46 Cluster table
Previous Message Vincent Bernat 2007-12-30 10:38:47 Re: Prefix search, word completion, full text search