> +1 for index organized tables
+1
I have a table:
CREATE TABLE mytab
(
"time" timestamp without time zone NOT NULL,
ne_id integer NOT NULL,
values integer,
CONSTRAINT mytab_pk PRIMARY KEY (ne_id, "time"),
CONSTRAINT mytab_ne_id_key UNIQUE ("time", ne_id)
}
The table is written every 15 minutes (that is, every 15 minutes all 20000 ne_ids get written), so the table is
"naturally"clustered on ("time", ne_id).
Since I need it clustered on (ne_id, "time"), I tried to cluster on a day by day basis, since clustering the whole
tablewould take too much time: that is, I'd "reorder" the table every day (say at 1:00 AM).
I've written a function (below) that re-insterts rows in the table ordered by ne_id,time; but it doesn't work! When I
doa "select * from mytab" rows aren't ordered by (ne_id,time)....
What am I doing wrong?
CREATE OR REPLACE FUNCTION somefunc()
RETURNS void AS
$BODY$
DECLARE
t1 timestamp := '2006-10-01 00:00:00';
t2 timestamp := '2006-10-01 23:59:00';
BEGIN
lock table stscell60_60_13_2800_512_cell_0610_leo in ACCESS EXCLUSIVE MODE;
WHILE t1 < '2006-11-01 00:00:00' LOOP
insert into mytab select time,ne_id+100000, values from mytab where time between t1 and t2 order by ne_id,time;
DELETE from mytab where time between t1 and t2 and ne_id<100000;
update mytab set ne_id = ne_id - 100000 where time between t1 and t2;
t1 := t1 + interval '1 days';
t2 := t2 + interval '1 days';
END LOOP ;
END;
$BODY$
LANGUAGE 'plpgsql'