Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id E3460476061
	for <pgsql-general@postgresql.org>;
	Fri,  4 Oct 2002 08:25:32 -0400 (EDT)
Received: from sldap2.mantraonline.com (unknown [202.56.250.12])
	by postgresql.org (Postfix) with SMTP id E6D9B475F5F
	for <pgsql-general@postgresql.org>;
	Fri,  4 Oct 2002 08:25:27 -0400 (EDT)
Received: (qmail 28645 invoked from network); 4 Oct 2002 12:01:34 -0000
Received: from unknown (HELO bharathi) ([203.145.160.35]) (envelope-sender
	<tech@leatherlink.net>)
	by himalaya.mantraonline.com (qmail-ldap-1.03) with SMTP
	for <pgsql-general@postgresql.org>; 4 Oct 2002 12:01:34 -0000
Content-Type: text/plain;
  charset="iso-8859-1"
From: Siva Kumar <tech@leatherlink.net>
To: pgsql-general@postgresql.org
Subject: Re: Query optimization
Date: Fri, 4 Oct 2002 17:56:35 +0530
User-Agent: KMail/1.4.1
References: <200210041347.55055.tech@leatherlink.net>
	<200210041114.17241.dev@archonet.com>
In-Reply-To: <200210041114.17241.dev@archonet.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200210041756.35489.tech@leatherlink.net>
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200210/181
X-Sequence-Number: 31179

On Friday 04 Oct 2002 3:44 pm, you wrote:
> > 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 he=
re
> > too).
>
> If that's the way the design makes sense, stick with it. It's better to g=
et
> Postgresql to handle a clean design rather than mangle a design.

We had a relook at the design and managed to add one reduntant field in a=
=20
table which made sense otherwise also. This shaved some time from the earli=
er=20
query. now the query looks like this.=20
=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=20
"Company",product_category_desc as "Product",uom_desc as=20
"UOM",shipment_term_desc as "Shipment Term",payment_term_desc as "Payment=
=20
Term",city_name as "Port",currency_name as "Currency",activity_desc as=20
"Activity",credit_days as "Credit Days"=20

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 rs.partner_id ORDER=
 BY=20
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

> First stage though, run an EXPLAIN and if you need help understanding it
> post the output back to the list.

Giving below the output of EXPLAIN ANALYSE. I could not make much sense out=
 of=20
it, please help!

NOTICE:  QUERY PLAN:

Sort  (cost=3D1382.45..1382.45 rows=3D1000 width=3D442) (actual time=3D3.47=
..3.47=20
rows=3D3 loops=3D1)
  ->  Merge Join  (cost=3D1263.12..1332.62 rows=3D1000 width=3D442) (actual=
=20
time=3D3.21..3.27 rows=3D3 loops=3D1)
        ->  Index Scan using master_activity_pkey on master_activity ma=20=
=20
(cost=3D0.00..52.00 rows=3D1000 width=3D50) (actual time=3D0.06..0.09 rows=
=3D4 loops=3D1)
        ->  Sort  (cost=3D1263.12..1263.12 rows=3D1000 width=3D392) (actual=
=20
time=3D3.04..3.05 rows=3D3 loops=3D1)
              ->  Merge Join  (cost=3D1143.79..1213.29 rows=3D1000 width=3D=
392)=20
(actual time=3D2.87..2.95 rows=3D3 loops=3D1)
                    ->  Index Scan using master_member_pkey on master_membe=
r=20
mm  (cost=3D0.00..52.00 rows=3D1000 width=3D47) (actual time=3D0.03..0.23 r=
ows=3D45=20
loops=3D1)
                    ->  Sort  (cost=3D1143.79..1143.79 rows=3D1000 width=3D=
345)=20
(actual time=3D2.48..2.48 rows=3D3 loops=3D1)
                          ->  Merge Join  (cost=3D1024.46..1093.96 rows=3D1=
000=20
width=3D345) (actual time=3D2.30..2.41 rows=3D3 loops=3D1)
                                ->  Index Scan using=20
master_product_category_pkey on master_product_category mpc=20=20
(cost=3D0.00..52.00 rows=3D1000 width=3D37) (actual time=3D0.02..0.08 rows=
=3D7 loops=3D1)
                                ->  Sort  (cost=3D1024.46..1024.46 rows=3D1=
000=20
width=3D308) (actual time=3D2.17..2.17 rows=3D3 loops=3D1)
                                      ->  Merge Join  (cost=3D905.13..974.6=
3=20
rows=3D1000 width=3D308) (actual time=3D1.96..2.10 rows=3D3 loops=3D1)
                                            ->  Index Scan using=20
master_uom_pkey on master_uom mu  (cost=3D0.00..52.00 rows=3D1000 width=3D4=
1)=20
(actual time=3D0.03..0.06 rows=3D6 loops=3D1)
                                            ->  Sort  (cost=3D905.13..905.1=
3=20
rows=3D1000 width=3D267) (actual time=3D1.87..1.87 rows=3D3 loops=3D1)
                                                  ->  Merge Join=20=20
(cost=3D785.80..855.30 rows=3D1000 width=3D267) (actual time=3D1.70..1.77 r=
ows=3D3=20
loops=3D1)
                                                        ->  Index Scan usin=
g=20
member_product_details_pkey on member_product_details mpd  (cost=3D0.00..52=
.00=20
rows=3D1000 width=3D23) (actual time=3D0.03..0.11 rows=3D14 loops=3D1)
                                                        ->  Sort=20=20
(cost=3D785.80..785.80 rows=3D1000 width=3D244) (actual time=3D1.53..1.53 r=
ows=3D3=20
loops=3D1)
                                                              ->  Merge Joi=
n=20=20
(cost=3D666.47..735.97 rows=3D1000 width=3D244) (actual time=3D1.35..1.47 r=
ows=3D3=20
loops=3D1)
                                                                    ->  Ind=
ex=20
Scan using master_shipment_term_pkey on master_shipment_term mst=20=20
(cost=3D0.00..52.00 rows=3D1000 width=3D50) (actual time=3D0.02..0.06 rows=
=3D5 loops=3D1)
                                                                    ->  Sor=
t=20=20
(cost=3D666.47..666.47 rows=3D1000 width=3D194) (actual time=3D1.27..1.27 r=
ows=3D3=20
loops=3D1)
                                                                          -=
>=20=20
Merge Join  (cost=3D547.14..616.64 rows=3D1000 width=3D194) (actual time=3D=
1.12..1.19=20
rows=3D3 loops=3D1)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20
->  Index Scan using master_city_pkey on master_city mcy  (cost=3D0.00..52.=
00=20
rows=3D1000 width=3D47) (actual time=3D0.02..0.04 rows=3D4 loops=3D1)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20
->  Sort  (cost=3D547.14..547.14 rows=3D1000 width=3D147) (actual time=3D1.=
05..1.06=20
rows=3D3 loops=3D1)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20
->  Merge Join  (cost=3D427.82..497.32 rows=3D1000 width=3D147) (actual=20
time=3D0.92..1.00 rows=3D3 loops=3D1)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
->  Index Scan using master_currency_pkey on master_currency mc=20=20
(cost=3D0.00..52.00 rows=3D1000 width=3D40) (actual time=3D0.03..0.06 rows=
=3D4 loops=3D1)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
->  Sort  (cost=3D427.82..427.82 rows=3D1000 width=3D107) (actual time=3D0.=
81..0.81=20
rows=3D3 loops=3D1)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
->  Merge Join  (cost=3D308.49..377.99 rows=3D1000 width=3D107) (actual=20
time=3D0.69..0.74 rows=3D3 loops=3D1)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20
->  Index Scan using member_financial_details_pkey on=20
member_financial_details mfd  (cost=3D0.00..52.00 rows=3D1000 width=3D11) (=
actual=20
time=3D0.03..0.08 rows=3D11 loops=3D1)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20
->  Sort  (cost=3D308.49..308.49 rows=3D1000 width=3D96) (actual time=3D0.5=
6..0.57=20
rows=3D3 loops=3D1)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20
->  Merge Join  (cost=3D189.16..258.66 rows=3D1000 width=3D96) (actual=20
time=3D0.46..0.51 rows=3D3 loops=3D1)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
->  Index Scan using master_payment_term_pkey on master_payment_term mpt=20=
=20
(cost=3D0.00..52.00 rows=3D1000 width=3D50) (actual time=3D0.02..0.04 rows=
=3D4 loops=3D1)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
->  Sort  (cost=3D189.16..189.16 rows=3D1000 width=3D46) (actual time=3D0.3=
5..0.35=20
rows=3D3 loops=3D1)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
->  Merge Join  (cost=3D69.83..139.33 rows=3D1000 width=3D46) (actual=20
time=3D0.21..0.25 rows=3D3 loops=3D1)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20
->  Index Scan using member_commercial_details_pkey on=20
member_commercial_details mcd  (cost=3D0.00..52.00 rows=3D1000 width=3D8) (=
actual=20
time=3D0.02..0.06 rows=3D6 loops=3D1)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20
->  Sort  (cost=3D69.83..69.83 rows=3D1000 width=3D38) (actual time=3D0.11.=
.0.11=20
rows=3D3 loops=3D1)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20
->  Seq Scan on _100001relationships rs  (cost=3D0.00..20.00 rows=3D1000=20
width=3D38) (actual time=3D0.03..0.05 rows=3D3 loops=3D1)
Total runtime: 5.45 msec

=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

Best regards,


Siva Kumar