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: 20041117205857.23009.qmail@web51307.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi All,
 
I have the following query:
 

SELECT mi.name, mi.item_id, mpd.quantity, mpd.unit_price, mpd.id, mcv.custom FROM m_pur_det mpd JOIN m_item mi ON mpd.item_id = mi.id LEFT JOIN m_cust_var mcv ON mpd.id=mcv.detail_id 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

                                                           ('public.m_item_id_seq'::text)
 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

                                    ('public.m_pur_det_id_seq'::text)
 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

                                                     ('public.m_cust_var_id_seq'::text)
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??

 

Thanks,

Saranya


			
---------------------------------
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-2014 The PostgreSQL Global Development Group