From: jamcito <jamcito@poczta.fm>
To: pgsql-performance@postgresql.org
Subject: partition text/varchar check problem
Date: Sat, 16 Dec 2006 15:29:40 +0100
Message-ID: <458402D4.7080500@poczta.fm> (raw)
Hello,
I am trying to make partitions:
CREATE SEQUENCE data_seq;
CREATE TABLE data (
identyf bigint,
name varchar,
added timestamp default now()
);
/*********************************************/
CREATE TABLE data_a (CHECK (name LIKE varchar 'a%')
) INHERITS (data);
--
CREATE TABLE data_b (CHECK (name LIKE varchar 'b%')
) INHERITS (data);
/*********************************************/
CREATE INDEX data_a_idx ON data_a(name);
CREATE INDEX data_b_idx ON data_b(name);
/*********************************************/
CREATE RULE data_insert_a AS ON INSERT TO data WHERE (name LIKE 'a%')
DO INSTEAD INSERT INTO data_a(identyf,name) VALUES
(nextval('data_seq'),NEW.name);
--
CREATE RULE data_insert_b AS ON INSERT TO data WHERE (name LIKE 'b%')
DO INSTEAD INSERT INTO data_b(identyf,name) VALUES
(nextval('data_seq'),NEW.name);
I put some data and vacuum:
/*********************************************/
INSERT INTO data(name) VALUES ('aaa');
INSERT INTO data(name) VALUES ('aab');
INSERT INTO data(name) VALUES ('baa');
INSERT INTO data(name) VALUES ('bab');
VACUUM ANALYZE data_a;
VACUUM ANALYZE data_b;
/*********************************************/
SET constraint_exclusion=off;
SET
EXPLAIN SELECT * FROM data WHERE name = 'aaa';
QUERY PLAN
------------------------------------------------------------------------
Result (cost=0.00..24.42 rows=7 width=48)
-> Append (cost=0.00..24.42 rows=7 width=48)
-> Seq Scan on data (cost=0.00..22.38 rows=5 width=48)
Filter: ((name)::text = 'aaa'::text)
-> Seq Scan on data_a data (cost=0.00..1.02 rows=1 width=23)
Filter: ((name)::text = 'aaa'::text)
-> Seq Scan on data_b data (cost=0.00..1.02 rows=1 width=23)
Filter: ((name)::text = 'aaa'::text)
(8 rows)
/*********************************************/
SET constraint_exclusion=on;
SET
SHOW constraint_exclusion;
constraint_exclusion
----------------------
on
(1 row)
EXPLAIN SELECT * FROM data WHERE name = 'aaa';
QUERY PLAN
------------------------------------------------------------------------
Result (cost=0.00..24.42 rows=7 width=48)
-> Append (cost=0.00..24.42 rows=7 width=48)
-> Seq Scan on data (cost=0.00..22.38 rows=5 width=48)
Filter: ((name)::text = 'aaa'::text)
-> Seq Scan on data_a data (cost=0.00..1.02 rows=1 width=23)
Filter: ((name)::text = 'aaa'::text)
-> Seq Scan on data_b data (cost=0.00..1.02 rows=1 width=23)
Filter: ((name)::text = 'aaa'::text)
(8 rows)
I have tried with name as text in data table and in CHECK. Where do I
have an error? Is it possible to make partitions with strings?
Thank you for any clues.
Best regards,
jamcito
----------------------------------------------------------------------
smieszne, muzyka, pilka, sexy, kibice, kino, ciekawe, extreme, kabaret
http://link.interia.pl/f19d4 - najlepsze filmy w intermecie
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jamcito@poczta.fm
Subject: Re: partition text/varchar check problem
In-Reply-To: <458402D4.7080500@poczta.fm>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox