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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

jamcito <jamcito@poczta.fm> writes:
> I am trying to make partitions:

> CREATE TABLE data_a (CHECK (name LIKE varchar 'a%')
> ) INHERITS (data);
> --
> CREATE TABLE data_b (CHECK (name LIKE varchar 'b%')
> ) INHERITS (data);

That's not going to work because the planner is unable to prove anything
about the behavior of LIKE --- there is nothing in the system that
offers a relationship between the = operator and the LIKE operator.
You'll need something like

CHECK (name >= 'a' AND name < 'b')
CHECK (name >= 'b' AND name < 'c')

etc. (These work for a query like "WHERE name = 'foo'" because
the >= < and = operators are all members of the same btree opclass,
so the planner knows how to reason about them.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Tom Lane wrote:
>> CREATE TABLE data_a (CHECK (name LIKE varchar 'a%')
>> ) INHERITS (data);
>> --
>> CREATE TABLE data_b (CHECK (name LIKE varchar 'b%')
>> ) INHERITS (data);
>
> That's not going to work because the planner is unable to prove anything
> about the behavior of LIKE --- there is nothing in the system that
> offers a relationship between the = operator and the LIKE operator.
> You'll need something like
>
> CHECK (name >= 'a' AND name < 'b')
> CHECK (name >= 'b' AND name < 'c')
>
> etc. (These work for a query like "WHERE name = 'foo'" because
> the >= < and = operators are all members of the same btree opclass,
> so the planner knows how to reason about them.)
>
> regards, tom lane

Thank you, it works!

Cheers,
jamcito

----------------------------------------------------------------------
Jestes kierowca? To poczytaj! >>> http://link.interia.pl/f199e


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

jamcito napisa³(a):
> /*********************************************/
> 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)
>
Can you show what you get from:
EXPLAIN SELECT * FROM data WHERE name LIKE 'a%'

?

Irek.


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Ireneusz Pluta wrote:
> Can you show what you get from:
> EXPLAIN SELECT * FROM data WHERE name LIKE 'a%'
>
> ?
>
> Irek.

I get:
QUERY PLAN
------------------------------------------------------------------------
Result (cost=0.00..24.42 rows=8 width=48)
-> Append (cost=0.00..24.42 rows=8 width=48)
-> Seq Scan on data (cost=0.00..22.38 rows=5 width=48)
Filter: ((name)::text ~~ 'a%'::text)
-> Seq Scan on data_a data (cost=0.00..1.02 rows=2 width=23)
Filter: ((name)::text ~~ 'a%'::text)
-> Seq Scan on data_b data (cost=0.00..1.02 rows=1 width=23)
Filter: ((name)::text ~~ 'a%'::text)
(8 rows)

Both partition tables are scanned.

Best,
jamcito

----------------------------------------------------------------------
smieszne, muzyka, pilka, sexy, kibice, kino, ciekawe, extreme, kabaret
http://link.interia.pl/f19d4 - najlepsze filmy w intermecie


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Ireneusz Pluta <ipluta@wp.pl> writes:
> Can you show what you get from:
> EXPLAIN SELECT * FROM data WHERE name LIKE 'a%'

Won't help. Exact equality of the WHERE condition is useful for
partial-index cases, because there the planner needs to prove that
the WHERE condition implies the index predicate before it can use
the index; and exact equality is certainly sufficient for that.
But for constraint exclusion, the problem is to prove that the
WHERE condition refutes the constraint, rather than implies it.
Knowing that "name LIKE 'a%'" disproves "name LIKE 'b%'" requires
more knowledge about LIKE than the planner has got.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly