>The database already knows exactly where to look for each
>table when all the tables are in the same file. All it
>has to do is "lseek()" to the appropriate spot. How does
>moving tables into separate files help this or make it any
>faster?
What I had in my mind is this:
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);
CREATE TABLE measurement_yy04mm02 (
CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
) INHERITS (measurement);
CREATE TABLE measurement_yy04mm03 (
CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
) INHERITS (measurement);
--next 2 are inserted into measurement_yy04mm02
INSERTI INTO measurement VALUES(1, '2004-02-15', 1, 1);
INSERTI INTO measurement VALUES(1, '2004-02-15', 1, 2);
--next 2 are inserted into measurement_yy04mm03
INSERTI INTO measurement VALUES(1, '2004-03-15', 1, 1);
INSERTI INTO measurement VALUES(1, '2004-03-15', 1, 2);
SELECT * FROM measurement WHERE logdate = '2004-02-15'; returns rows from
measurement_yy04mm02
and
SELECT * FROM measurement; returns rows from both/all tables.
all queries are should now be a lot fasters. Don't you agree?
Regards,
Miha
