Re: Searching union views not using indices

> Hello everyone.
>
> We are facing a performance problem with views consisting of several
> unioned tables. The simplified schema is as follows:
>
> CREATE TABLE foo (
> foo_object_id bigint,
> link_id bigint,
> somedata text,
> PRIMARY KEY (foo_object_id) );

point 1:
well, you may want to consider:

create table foobar
(
prefix text, -- foo/bar/etc
object_id bigint,
link_id bigint,
primary key(prefix, object_id)
); -- add indexes as appropriate

and push foo/bar specific information to satellite table which refer
back via pkey-key link. Now you get very quick and easy link id query
and no view is necessary. You also may want to look at table
inheritance but make sure you read all the disclaimers first.

point 2:
watch out for union, it is implied sort and duplicate filter. union all
is faster although you may get duplicates.

Merlin