Re: Query optimization

Siva Kumar wrote:

>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
>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
>
>
http://www.postgresql.org/idocs/index.php?performance-tips.html
First, read this documentation.

Thanks
Antonis