Thread

1. Table join performance threshold...

   Bryan Campbell <bryan@wordsandimages.com> — 2000-06-16T23:09:21Z

   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
   (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
   
   
   

2. Re: Table join performance threshold...

   Karl F. Larsen <k5di@zianet.com> — 2000-06-20T22:47:41Z

   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@arrl.net  (505) 524-3303  -