From: | "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> |
---|---|
To: | "Michal Taborsky" <michal(dot)taborsky(at)mall(dot)cz> |
Cc: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Searching union views not using indices |
Date: | 2005-11-04 15:07:53 |
Message-ID: | 6EE64EF3AB31D5448D0007DD34EEB3417DD7C6@Herge.rcsinc.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2005-11-04 15:12:23 | Re: insert performance for win32 |
Previous Message | Richard Huxton | 2005-11-04 15:01:05 | Re: Searching union views not using indices |