From: Siva Kumar <tech@leatherlink.net>
To: pgsql-general@postgresql.org
Subject: Query optimization
Date: Fri, 4 Oct 2002 13:47:55 +0530
Message-ID: <200210041347.55055.tech@leatherlink.net> (raw)
We have a query as below:
=========================================================
select relationship_id as "Id",company_name as "Company",product_category_desc
as "Product",uom_desc as "UOM",shipment_term_desc as "Shipment
Term",payment_term_desc as "Payment Term",city_name as "Port",currency_name
as "Currency",activity_desc as "Activity",credit_days as "Credit Days" from
_100001relationships rs,master_member mm,member_product_details
mpd,master_product_category mpc,master_uom mu,master_shipment_term
mst,master_payment_term mpt,member_financial_details mfd,master_currency
mc,member_commercial_details mcd,master_activity ma,master_city mcy
where mpd.product_details_id = rs.product_details_id and
mpd.product_category_id=mpc.product_category_id and mpd.uom_id = mu.uom_id
and rs.shipment_term_id = mst.shipment_term_id and rs.payment_term_id =
mpt.payment_term_id and rs.commercial_details_id = mcd.commercial_details_id
and mcd.port = mcy.city_id and rs.financial_details_id =
mfd.financial_details_id and mfd.currency_id = mc.currency_id and
mpd.activity_id = ma.activity_id and mm.member_id = mpd.member_id
ORDER BY rs.relationship_id DESC;
=====================================================
The decision to keep the fields in different tables was taken in view of the
overall need of the system (there might be scope for improvement here too).
This query normally select about 10-20 rows. The problem is, the page load
takes about 4-5 seconds in the local network. The query run in psql terminal
takes about 2 second to execute (outputing 3 rows).
When hosted on the internet with most of our users using dialup connections,
and the query returning 10+ rows, this will not be acceptable.
Please point us towards the right direction to handle this type of problems.
Best regards,
Siva Kumar
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: tech@leatherlink.net
Subject: Re: Query optimization
In-Reply-To: <200210041347.55055.tech@leatherlink.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox