I have a table that has over 100K rows of GIS data, including a raster and an
insertdatetime timestamp columns. This table is continually loaded with data
with processes on the back side querying the data and populating other tables
depending on characteristics of the data. Today a row is read, processed,
then deleted. Vacuums occur frequently and are quite time consuming.
I figured a scheme of partitioning the table into 7 child tables, one for each
day of the week, the day derived from the 'insertdatetime' value. Thus, there
is the master, 'incoming_grid', and 7 children, 'incoming_grid_sun',
'incoming_grid_mon', 'incoming_grid_tue', etc. A job would be kicked off each
night after midnight that could then skip the current and the previous days and
truncate the tables for the remaining 5 days, speeding the whole process up
since there would be no deletes or frequent vacuums. I even figured on
creating a separate tablespace for the child tables. Here's my DDLs -
CREATE TABLE incoming_grid
(
rid integer NOT NULL DEFAULT nextval('incoming_grid_rid_seq'::regclass), --
record id
rast raster,
model character varying(80) NOT NULL,
parameter character varying(80) NOT NULL,
forecast bigint NOT NULL,
level character varying(128) NOT NULL,
insertdatetime timestamp without time zone DEFAULT (now())::timestamp without
time zone,
rundatetime timestamp without time zone NOT NULL,
CONSTRAINT incoming_grid_pkey PRIMARY KEY (forecast, parameter, level, model,
rundatetime)
);
CREATE INDEX "incoming_grid_Index1"
ON incoming_grid
USING btree
(parameter, forecast, level, rundatetime);
CREATE TABLE incoming_grid_sun
(
CHECK (to_char(insertdatetime, 'dy') = 'sun')
) INHERITS (incoming_grid);
CREATE INDEX "incoming_grid_sun_Index1"
ON incoming_grid_sun
USING btree
(parameter , forecast, level, rundatetime);
...
CREATE TABLE incoming_grid_sat
(
CHECK (to_char(insertdatetime, 'dy') = 'sat')
) INHERITS (incoming_grid);
CREATE INDEX "incoming_grid_sat_Index1"
ON incoming_grid_sat
USING btree
(parameter , forecast, level, rundatetime);
CREATE TRIGGER incoming_grid_load_trigger
BEFORE INSERT OR UPDATE
ON incoming_grid
FOR EACH ROW
EXECUTE PROCEDURE incoming_grid_load();
CREATE OR REPLACE FUNCTION incoming_grid_load()
RETURNS trigger AS
$BODY$
DECLARE
schema TEXT='children';
tablename TEXT;
query TEXT;
BEGIN
tablename = 'incoming_grid_' || to_char(NEW.insertdatetime, 'dy');
IF TG_OP = 'INSERT' THEN
query = 'INSERT INTO ' || tablename || ' SELECT $1.*;';
EXECUTE(query) USING NEW;
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT
COST 100;
So far, so good. I loaded the master with 100 rows and they all went to their
correct child table.
My problem now is that SELECTs scan all child tables - the CHECK constraint
doesn't factor in. I tried the following to create another index on the
master, but it didn't work, either - all partitions are scanned. Any ideas on
how I can resolve this?
CREATE FUNCTION custom_to_char(timestamp) RETURNS text AS
$$ select to_char($1, 'dy'); $$
LANGUAGE sql immutable;
CREATE INDEX "incoming_grid_Index2"
ON incoming_grid
USING btree
(custom_to_char(insertdatetime));
Thanks,
Steve Erickson
NOTICE: This email message is for the sole use of the intended recipient(s) and
may contain confidential and privileged information. Any unauthorized use,
disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply email and destroy all copies of
the original message.
