Christian Smith wrote:
On Tue, 21 Mar 2006, Miha Vrhovnik wrote:
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?
I wouldn't agree. Not only do we now have to traverse whatever index/table
contains the result, we also have to evaluate any condition just to
determine which table to traverse.
Where table partitioning may be a win on other DBMS is where you have
different tables in different table spaces, which are usually configured
on different devices, and allows access to multiple rows in the 'virtual'
table to be accessed in parallel from the multiple devices. It also allows
the table to be larger than any single tablespace.
Regards,
Miha
Christian
When machines had disk farms, elaborate disk controllers and not much
memory having database software make use of multiple spindles was sound
performance practice. Machines are different now and may have gigabytes
of memory.
