Skip site navigation (1) Skip section navigation (2)

Optimizing a query

From: sarlav kumar <sarlavk(at)yahoo(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Optimizing a query
Date: 2004-11-17 20:58:56
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Hi All,
I have the following query:

SELECT, mi.item_id, mpd.quantity, mpd.unit_price,, mcv.custom FROM m_pur_det mpd JOIN m_item mi ON mpd.item_id = LEFT JOIN m_cust_var mcv ON WHERE mpd.purchase_id = 79072;


Details of the tables:


m_item : PK- id


 Column            |          Type               |                      Modifiers                           
 mer_id             | integer                      | 
 name               | character varying(20)  | 
 description       | character varying(250) | 
 price                | integer                       | 
 max_quantity   | integer                       | 
 id                    | integer                       | not null default nextval

 item_id            | character varying(10)  | 



m_pur_det: PK -id 


Column         |  Type    | Modifiers                                 
 purchase_id  | integer  | not null
 item_id         | integer  | not null
 quantity        | integer  | not null
  id                | integer  | not null default nextval

 unit_price     | integer | not null
Indexes: m_pur_det_pkey primary key btree (id),
         m_pur_det_purchase_id_key unique btree (purchase_id, item_id)
FK constraints: (item_id) REFERENCES m_item(id) 

m_cust_var : PK - id


Column    |          Type                  |        Modifiers                              
 id            | integer                         | not null default nextval

detail_id   | integer                         | 
 custom    | character varying(100) | 
Indexes: m_cust_var_pkey primary key btree (id),
         m_cust_var_detail_id_key unique btree (detail_id)
Foreign Key: (detail_id) REFERENCES m_pur_det(id)


Now my question: 

I would like to know if there is a way to re-write the query using sub-select. And will that improve the performance??




Do you Yahoo!?
 The all-new My Yahoo!  Get yours free!    

pgsql-novice by date

Next:From: sarlav kumarDate: 2004-11-17 21:23:35
Subject: automated mail from Arinbe!??!
Previous:From: sarlav kumarDate: 2004-11-17 20:08:24
Subject: Re: subscribe for a digest fails

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group