Should I create an index for partition with fixed key?
Hello.
Some days ago I started using partitioning to make it possible to work
with large amount of data.
I created master table as documentation suggest, created some child
tables which inherit from the master one and then added a constraint
for each child table to define the allowed key value. Constraint was
like:
CHECK (site_id = 'google_com')
Now here is my question:
While reading the documentation I came across the following
suggestion: "For each partition, create an index on the key column(s)"
Should I do that if the key will be the same for the whole partition?
Will there be any benefits of having an index on site_id column?
When I first read it, I decided that if all rows have the same key
(say site_id = 'google_com') then there is no need in index.
Thanks.
--
Sergey Samokhin
Hello.
Should I do that if the key will be the same for the whole partition?
Will there be any benefits of having an index on site_id column?
It looks like there is no any need in index on the site_id column for
partitions.
Here is SQL illustraing DB structure and typical usage (which I should
have listed in the original letter):
CREATE TABLE site ( site_id varchar(50) PRIMARY KEY, descr text);
-- req is a master table
CREATE TABLE req ( site_id varchar(50), timestamp timestamp );
-- now let's create a few child tables
INSERT INTO site (site_id) VALUES ('google_com'); CREATE TABLE
req_google_com ( CHECK (site_id = 'google_com') ) INHERITS (req);
INSERT INTO site (site_id) VALUES ('twitter_com'); CREATE TABLE
req_twitter_com ( CHECK (site_id = 'twitter_com') ) INHERITS (req);
-- and now write some requests:
INSERT INTO req_google_com VALUES ('google_com', now()), ('google_com', now());
--
Sergey Samokhin