Re: Searching union views not using indices - Mailing list pgsql-performance

> 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