Postgres Pro
Downloads
Home   >   mailing lists

Thread: partition text/varchar check problem

partition text/varchar check problem

From
jamcito
Date:
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

Re: partition text/varchar check problem

From
Tom Lane
Date:
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

Re: partition text/varchar check problem -- solved

From
jamcito
Date:
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

Re: partition text/varchar check problem

From
Ireneusz Pluta
Date:
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.

Re: partition text/varchar check problem

From
jamcito
Date:
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

Re: partition text/varchar check problem

From
Tom Lane
Date:
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