Re: Query optimization - Mailing list pgsql-general

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 here
> > too).
>
> If that's the way the design makes sense, stick with it. It's better to get
> 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
table which made sense otherwise also. This shaved some time from the earlier
query. now the query looks like this.
======================================
 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 = rs.partner_id ORDER BY
rs.relationship_id DESC;
==================================================

> 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
it, please help!

NOTICE:  QUERY PLAN:

Sort  (cost=1382.45..1382.45 rows=1000 width=442) (actual time=3.47..3.47
rows=3 loops=1)
  ->  Merge Join  (cost=1263.12..1332.62 rows=1000 width=442) (actual
time=3.21..3.27 rows=3 loops=1)
        ->  Index Scan using master_activity_pkey on master_activity ma
(cost=0.00..52.00 rows=1000 width=50) (actual time=0.06..0.09 rows=4 loops=1)
        ->  Sort  (cost=1263.12..1263.12 rows=1000 width=392) (actual
time=3.04..3.05 rows=3 loops=1)
              ->  Merge Join  (cost=1143.79..1213.29 rows=1000 width=392)
(actual time=2.87..2.95 rows=3 loops=1)
                    ->  Index Scan using master_member_pkey on master_member
mm  (cost=0.00..52.00 rows=1000 width=47) (actual time=0.03..0.23 rows=45
loops=1)
                    ->  Sort  (cost=1143.79..1143.79 rows=1000 width=345)
(actual time=2.48..2.48 rows=3 loops=1)
                          ->  Merge Join  (cost=1024.46..1093.96 rows=1000
width=345) (actual time=2.30..2.41 rows=3 loops=1)
                                ->  Index Scan using
master_product_category_pkey on master_product_category mpc
(cost=0.00..52.00 rows=1000 width=37) (actual time=0.02..0.08 rows=7 loops=1)
                                ->  Sort  (cost=1024.46..1024.46 rows=1000
width=308) (actual time=2.17..2.17 rows=3 loops=1)
                                      ->  Merge Join  (cost=905.13..974.63
rows=1000 width=308) (actual time=1.96..2.10 rows=3 loops=1)
                                            ->  Index Scan using
master_uom_pkey on master_uom mu  (cost=0.00..52.00 rows=1000 width=41)
(actual time=0.03..0.06 rows=6 loops=1)
                                            ->  Sort  (cost=905.13..905.13
rows=1000 width=267) (actual time=1.87..1.87 rows=3 loops=1)
                                                  ->  Merge Join
(cost=785.80..855.30 rows=1000 width=267) (actual time=1.70..1.77 rows=3
loops=1)
                                                        ->  Index Scan using
member_product_details_pkey on member_product_details mpd  (cost=0.00..52.00
rows=1000 width=23) (actual time=0.03..0.11 rows=14 loops=1)
                                                        ->  Sort
(cost=785.80..785.80 rows=1000 width=244) (actual time=1.53..1.53 rows=3
loops=1)
                                                              ->  Merge Join
(cost=666.47..735.97 rows=1000 width=244) (actual time=1.35..1.47 rows=3
loops=1)
                                                                    ->  Index
Scan using master_shipment_term_pkey on master_shipment_term mst
(cost=0.00..52.00 rows=1000 width=50) (actual time=0.02..0.06 rows=5 loops=1)
                                                                    ->  Sort
(cost=666.47..666.47 rows=1000 width=194) (actual time=1.27..1.27 rows=3
loops=1)
                                                                          ->
Merge Join  (cost=547.14..616.64 rows=1000 width=194) (actual time=1.12..1.19
rows=3 loops=1)

->  Index Scan using master_city_pkey on master_city mcy  (cost=0.00..52.00
rows=1000 width=47) (actual time=0.02..0.04 rows=4 loops=1)

->  Sort  (cost=547.14..547.14 rows=1000 width=147) (actual time=1.05..1.06
rows=3 loops=1)

->  Merge Join  (cost=427.82..497.32 rows=1000 width=147) (actual
time=0.92..1.00 rows=3 loops=1)

->  Index Scan using master_currency_pkey on master_currency mc
(cost=0.00..52.00 rows=1000 width=40) (actual time=0.03..0.06 rows=4 loops=1)

->  Sort  (cost=427.82..427.82 rows=1000 width=107) (actual time=0.81..0.81
rows=3 loops=1)

->  Merge Join  (cost=308.49..377.99 rows=1000 width=107) (actual
time=0.69..0.74 rows=3 loops=1)

->  Index Scan using member_financial_details_pkey on
member_financial_details mfd  (cost=0.00..52.00 rows=1000 width=11) (actual
time=0.03..0.08 rows=11 loops=1)

->  Sort  (cost=308.49..308.49 rows=1000 width=96) (actual time=0.56..0.57
rows=3 loops=1)

->  Merge Join  (cost=189.16..258.66 rows=1000 width=96) (actual
time=0.46..0.51 rows=3 loops=1)

->  Index Scan using master_payment_term_pkey on master_payment_term mpt
(cost=0.00..52.00 rows=1000 width=50) (actual time=0.02..0.04 rows=4 loops=1)

->  Sort  (cost=189.16..189.16 rows=1000 width=46) (actual time=0.35..0.35
rows=3 loops=1)

  
->  Merge Join  (cost=69.83..139.33 rows=1000 width=46) (actual
time=0.21..0.25 rows=3 loops=1)

        
->  Index Scan using member_commercial_details_pkey on
member_commercial_details mcd  (cost=0.00..52.00 rows=1000 width=8) (actual
time=0.02..0.06 rows=6 loops=1)

        
->  Sort  (cost=69.83..69.83 rows=1000 width=38) (actual time=0.11..0.11
rows=3 loops=1)

              
->  Seq Scan on _100001relationships rs  (cost=0.00..20.00 rows=1000
width=38) (actual time=0.03..0.05 rows=3 loops=1)
Total runtime: 5.45 msec

=====================================================

Best regards,


Siva Kumar