Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id B8344476BFE
	for <pgsql-general@postgresql.org>;
	Fri,  4 Oct 2002 04:16:46 -0400 (EDT)
Received: from sldap2.mantraonline.com (unknown [202.56.250.12])
	by postgresql.org (Postfix) with SMTP id 487A1476B72
	for <pgsql-general@postgresql.org>;
	Fri,  4 Oct 2002 04:16:42 -0400 (EDT)
Received: (qmail 21966 invoked from network); 4 Oct 2002 07:52:51 -0000
Received: from unknown (HELO bharathi) ([203.145.160.76]) (envelope-sender
	<tech@leatherlink.net>)
	by himalaya.mantraonline.com (qmail-ldap-1.03) with SMTP
	for <pgsql-general@postgresql.org>; 4 Oct 2002 07:52:51 -0000
Content-Type: text/plain;
  charset="us-ascii"
From: Siva Kumar <tech@leatherlink.net>
To: pgsql-general@postgresql.org
Subject: Query optimization
Date: Fri, 4 Oct 2002 13:47:55 +0530
User-Agent: KMail/1.4.1
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200210041347.55055.tech@leatherlink.net>
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200210/175
X-Sequence-Number: 31174


We have a query as below:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D
select relationship_id as "Id",company_name as "Company",product_category_d=
esc=20
as "Product",uom_desc as "UOM",shipment_term_desc as "Shipment=20
Term",payment_term_desc as "Payment Term",city_name as "Port",currency_name=
=20
as "Currency",activity_desc as "Activity",credit_days as "Credit Days" from

 _100001relationships rs,master_member mm,member_product_details=20
mpd,master_product_category mpc,master_uom mu,master_shipment_term=20
mst,master_payment_term mpt,member_financial_details mfd,master_currency=20
mc,member_commercial_details mcd,master_activity ma,master_city mcy

 where mpd.product_details_id =3D rs.product_details_id and=20
mpd.product_category_id=3Dmpc.product_category_id and mpd.uom_id =3D mu.uom=
_id=20
and rs.shipment_term_id =3D mst.shipment_term_id and rs.payment_term_id =3D=
=20
mpt.payment_term_id and rs.commercial_details_id =3D mcd.commercial_details=
_id=20
and mcd.port =3D mcy.city_id and rs.financial_details_id =3D=20
mfd.financial_details_id and mfd.currency_id =3D mc.currency_id and=20
mpd.activity_id =3D ma.activity_id and mm.member_id =3D mpd.member_id=20

ORDER BY rs.relationship_id DESC;
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D

The decision to keep the fields in different tables was taken in view of th=
e=20
overall need of the system (there might be scope for improvement here too).=
=20
This query normally select about 10-20 rows. The problem is, the page load=
=20
takes about 4-5 seconds in the local network. The query run in psql termina=
l=20
takes about 2 second to execute (outputing 3 rows).

When hosted on the internet with most of our users using dialup connections=
,=20
and the query returning 10+ rows, this will not be acceptable.=20

Please point us towards the right direction to handle this type of problems=
.=20

Best regards,

Siva Kumar