Tuning 7.2? Different than 7.1.3?

From: Hunter Hillegas <lists(at)lastonepicked(dot)com>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: Tuning 7.2? Different than 7.1.3?
Date: 2002-03-12 20:05:11
Message-ID: B8B39F77.34AEF%lists@lastonepicked.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I recently moved from 7.1.3 to 7.2. I am running on a Dual PIII900 Red Hat
7.1 server accessing Postgres exclusively through JDBC (JBoss app server is
on the same machine).

Since moving to 7.2, performance on part of my app really sucks. I'm sure it
is some setting or index that I'm missing...

Basically, certain queries run the CPU at 90% and I can't figure out why.
The tables are pretty simple...

Here's the scenario of a set of queries that peg the CPU:

DEBUG: query: SELECT product.rec_num FROM product , product_group AS pg
WHERE pg.name = 'Music' AND pg.rec_num = product.productgroup_products AND
product.active_status = true ORDER BY product_name
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: StartTransactionCommand
DEBUG: query: SELECT rec_num,product_name, product_id, description,
image_uri, active_status, ProductGroup_products FROM product WHERE
(rec_num=18) OR (rec_num=19)
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: StartTransactionCommand
DEBUG: query: SELECT product, rec_num FROM product_line_item WHERE
(product=18) OR (product=19)
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: StartTransactionCommand
DEBUG: query: SELECT product_id, price, description, discount, weight FROM
product_line_item WHERE (rec_num=19)
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: StartTransactionCommand
DEBUG: query: SELECT product_id, price, description, discount, weight FROM
product_line_item WHERE (rec_num=20)

The tables look like this:

Table "product"
Column | Type | Modifiers
-----------------------+-------------------+-----------
rec_num | integer | not null
active_status | boolean |
product_id | character varying |
product_name | character varying |
description | text |
image_uri | character varying |
productgroup_products | integer |
Primary key: pkproduct

Table "product_line_item"
Column | Type | Modifiers
-------------+-------------------+-----------
rec_num | integer | not null
product_id | character varying |
price | double precision |
description | text |
weight | double precision |
discount | double precision |
product | integer |
Primary key: pkproduct_line_item

Table "product_group"
Column | Type | Modifiers
---------+-------------------+-----------
rec_num | integer | not null
name | character varying |
Primary key: pkproduct_group

From this info, can anyone see why performance would really SUCK so bad? It
can take up to a minute for this to return...

product has 63 rows, product_line_item has 131 and product_group has 4.

Any help is appreciated.

Hunter

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jan Wieck 2002-03-12 20:11:30 Re: if possible execute a function a start/end connection
Previous Message Serkan Bektaş 2002-03-12 19:13:06 Re: cannot initdb ; semget(key=1, num=17, 03600) failed , FreeBSD