From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | "LORRAINE DEWEY" <LORRAINE(dot)DEWEY(at)companiongroup(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Optomizing left outer joins |
Date: | 2003-04-23 20:51:53 |
Message-ID: | 200304231351.53694.josh@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Lorraine,
> Perhaps I should have said that not only is the column optional, the entire
> row is optional. I am trying to join a master table (contains insurance
claim
> information) to an error table. If the claim in the master table doesn't
have
> any errors, there won't be a row for it in the error table. When I select
> columns from both tables, I only get the errored claims. That's why I was
> trying to use the outer join.
Well, an outer join is appropriate in that case. However, a single outer
join shouldn't be killing your queries -- in the example I gave, I eliminated
19 outer joins to boost the client's performance.
I think the lack of indexes is your real albatross.
> I like your advice about telling the customer about the workaround. I'll
> definitely do that! The workaround is pretty awkward: two select statements
> joined by "union all," then some subselects to pick out the unmatched
records.
> It returns the correct data and it's faster than the left join, but it
takes
> a lot more code. Anyway, I guess I'm stuck with it.
You probably are. But make sure you tell the client that the lack of indexes
is killing performance; even if it doesn't get fixed, your keister is
covered.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-04-24 02:37:38 | Re: Optomizing left outer joins |
Previous Message | Bruno Wolff III | 2003-04-23 20:29:26 | Re: Ok,, what about cache for SEQUENCE - where? how? |