Re: Table join performance threshold...

Your problem is obscure. I studied and then found it! See my stuff below:

On Fri, 16 Jun 2000, Bryan Campbell wrote:

> Howdy,
>
> I'm a newbie to postgres, and I'm sure I've run into an obvious problem.
>
> I have a database with around 20 or so tables. None of them are very large
> (60 rows X around 20 columns in the largest). Actually, the largest is my
> master product table with a bunch of ID's to entries in other tables
> (product attributes). Pretty standard stuff...
>
> What I want to do is select a row in that table, and then join about 15 or
> so tables with corresponding ID-Value relationships.
>
> My join works great... but it's slow. If I back the number of fields in my
> SELECT/WHERE query down to 9, it speeds up dramatically (almost
> instantaneous). Anything above 9 and it slows to a whopping 8 seconds.
>
> Why would I experience such a dramatic change in response? I'm not doing
> anything complex in my query... just your standard:
>
> SELECT parameter_table.field AS some_friendly_name, (more fields...)
> FROM master_table
> WHERE master_table.parameter_id = parameter_table.id AND
^^^^

This AND cannot exist as part of WHERE. WHERE is a condition. See page 170
of the PostgreSQL User's Guide you got in your package.

> (more joins...)
>
> The parameter tables are very simple 2 column tables (KEY, ATTRIBUTE), none
> of them over 40 rows.
>
> Any thoughts? Is my SQL statement bunk? Does it look like I'm hitting a
> memory limit? I've been reading quite a bit, but I'm having trouble finding
> a lead.
>
> Thanks for helping!!!!
>
>
> Bryan
>
>
>

Yours Truly,

- Karl F. Larsen, k5di(at)arrl(dot)net (505) 524-3303 -