pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpartition text/varchar check problem
5+ messages / 3 participants
[nested] [flat]
* partition text/varchar check problem
@ 2006-12-16 14:29 jamcito <jamcito@poczta.fm>
0 siblings, 2 replies; 5+ messages in thread
From: jamcito @ 2006-12-16 14:29 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: partition text/varchar check problem
@ 2006-12-16 15:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: jamcito <jamcito@poczta.fm>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2006-12-16 15:48 UTC (permalink / raw)
To: jamcito <jamcito@poczta.fm>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: partition text/varchar check problem
@ 2006-12-16 20:16 Ireneusz Pluta <ipluta@wp.pl>
parent: jamcito <jamcito@poczta.fm>
1 sibling, 2 replies; 5+ messages in thread
From: Ireneusz Pluta @ 2006-12-16 20:16 UTC (permalink / raw)
To: jamcito <jamcito@poczta.fm>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: partition text/varchar check problem
@ 2006-12-16 20:30 jamcito <jamcito@poczta.fm>
parent: Ireneusz Pluta <ipluta@wp.pl>
1 sibling, 0 replies; 5+ messages in thread
From: jamcito @ 2006-12-16 20:30 UTC (permalink / raw)
To: Ireneusz Pluta <ipluta@wp.pl>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: partition text/varchar check problem
@ 2006-12-16 20:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ireneusz Pluta <ipluta@wp.pl>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2006-12-16 20:34 UTC (permalink / raw)
To: Ireneusz Pluta <ipluta@wp.pl>; +Cc: jamcito <jamcito@poczta.fm>; pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-12-16 20:34 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-16 14:29 partition text/varchar check problem jamcito <jamcito@poczta.fm>
2006-12-16 15:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-12-16 20:16 ` Ireneusz Pluta <ipluta@wp.pl>
2006-12-16 20:30 ` jamcito <jamcito@poczta.fm>
2006-12-16 20:34 ` Tom Lane <tgl@sss.pgh.pa.us>
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