Greetings!
Just trying some tests out, and wanted to know about some optimizations.
If I do a CHECK constraint on a table, is this used to optimize a SELECT
or does Postgresql rely mostly on normal index search?
For example, I want to create some tables to manage different data in a
kind of <object, relationship, object2> manner, but where object2 could be
an IP address, text, a number, etc. So I thought of doing the following:
----------
create table tmp (
luid bigserial,
object_luid bigint,
relationship ltree
);
create table tmp1
(
child_luid bigint,
check (relationship <@ 'Object')
)
inherits (tmp);
create table tmp2 (
ip inet,
check (relationship <@ 'IP')
)
inherits (tmp);
insert into tmp1 (object_luid, relationship, child_luid) values (1, 'Object', 2);
insert into tmp2 (object_luid, relationship, ip) values (1, 'IP.Packet.Source', '10.1.1.2');
insert into tmp2 (object_luid, relationship, ip) values (2, 'IP.Packet.Source', '10.11.0.1');
create view tmp_view as
select luid, object_luid, relationship, child_luid, null as ip
from tmp1
union
select luid, object_luid, relationship, null, ip
from tmp2
;
explain analyze select * from tmp_view where object_luid = 2;
explain analyze select * from tmp_view where relationship <@ 'IP.Packet';
explain analyze select * from (
select luid, object_luid, relationship, child_luid, null as ip
from tmp1
union all
select luid, object_luid, relationship, null, ip
from tmp2
) as foo where relationship <@ 'IP.Packet';
;
-----------------------------------------
When I do the above analyzes, the table for <tmp1> is still scanned, even
though the WHERE clause cannot meet the CHECK clause. Now, this is a
fairly edge case for optimizations, so I just wanted to check that this
indeed will not be utilized. Or will it be only after I have lots of rows
in the table, thereby justifying the check? Is there ever a time where
CONSTRAINTS will be used to optimize a SELECT?
Alternatively, is there another way of accomplishing what I want without
the ugly VIEW (as each new table type I add will necessitate rebuilding
the VIEW with a new column)?
Regards!
Ed
