Re: left outer join terrible slow compared to inner join

> Perhaps the optimizer thinks the tables are just small enough
> to not make them
> worthwhile.
>
> Which columns actually have indexes, are they all the same
> data type as the
> value they're being compared against?

Yup, this was my guess. Many of the tables being joined in are not excessively large (10s or 100s of records), while tables like device and numplan are VERY large (or can be).

Most, if not all of the joins -- if memory serves -- are being made across foreign keys of either uuid type or simple ints.

The uuid type I created (implements libuuid as a type for postgresql), and, yes, I defined all the operator classes for btree-ing (it does work nicely).

I'm going to follow Tom's suggestion at try it against 7.4 next week. Until then...

cwl

> -----Original Message-----
> From: Greg Stark [mailto:gsstark(at)mit(dot)edu]
> Sent: Thursday, August 28, 2003 8:20 PM
> To: Mike Mascari
> Cc: Clay Luther; Greg Stark; pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] left outer join terrible slow compared to inner
> join
>
>
>
> Mike Mascari <mascarm(at)mascari(dot)com> writes:
>
> > > 1) Our database is highly normalized.
>
> If anything I was worried it was "excessively" normalized.
> Sometimes people go
> overboard, taking columns that really could be simple
> attributes and make them
> reference tables. But that usually doesn't cause performance
> problems, just
> programmer headaches. It was just a first impression, the
> simple number of
> tables isn't evidence.
>
> > > 2) All joins in the query are performed across indeces.
>
> Ok, well only one of the table accesses is actually using an
> index in that
> plan. I don't understand what's going on in enough detail to
> explain why.
>
> Perhaps the optimizer thinks the tables are just small enough
> to not make them
> worthwhile.
>
> Which columns actually have indexes, are they all the same
> data type as the
> value they're being compared against?
>
> --
> greg
>
>