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


From: "Thomas T(dot) Veldhouse" <veldy(at)veldy(dot)net>
To: "tamsin" <tg_mail(at)bryncadfan(dot)co(dot)uk>, "PostgreSQL-general" <pgsql-general(at)postgreSQL(dot)org>
Subject: Re:
Date: 2001-06-29 15:58:11
Message-ID: 003501c100b4$4ea2cd10$ (view raw, whole thread or download thread mbox)
Lists: pgsql-generalpgsql-sql
select pm.*
from purchasemaster pm
  inner join itemmaster im
    on pm.transactionid = im.transactionid
where im.itempriority = 2

Try this instead.

Make sure the appropriate indexes (on pm.transactionid, im.transactionid and im.itempriority) exist if the tables are of any appreciable size.  

Tom Veldhouse

  ----- Original Message ----- 
  From: tamsin 
  To: pgsql-general(at)postgresql(dot)org 
  Sent: Friday, June 29, 2001 7:40 AM
  Subject: RE: [GENERAL] 

  i think performance is better in postgres if you rewrite this sort of query as:

  select * from purchasemaster where exists (select 1 from itemmaster where itempriority = 2 and transactionid = purchasemaster.transactionid)

  hope this helps,

    -----Original Message-----
    From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Kapil Tilwani
    Sent: 28 May 2001 12:27
    To: pgsql-general(at)postgresql(dot)org; pgsql-sql(at)postgresql(dot)org
    Subject: [GENERAL] 


    I have two tables purchasemaster (Master) and ItemMaster (child) linked through TransactionID

    I have a query like
    select * from purchasemaster where transactionid in (select transactionid from itemmaster where itempriority = 2)

    ****I need this query for MS-Access, MS-SQL and Postgres.  Because the application is supposed to be such that for upto 2-3 users, the application would be running on MS-Access or MSDE, while for heavier databases i.e., greater than 4 , the ideal database would be Postgres

    Only the problem is ...  (correct me if i am wrong, PLEEEEEEEEEEEASE) that for each tuple in purchasemaster the sub-query is re-evaluated (True or False????).  What I am looking at is whether it is possible to execute the query in the sub-query just once, get its value as in "(1,2,5,23,7,90)", etc. in place of the sub-query and accordingly, execute the main query.

    Thank you,

In response to

  • RE: at 2001-06-29 12:40:04 from tamsin


  • Re: at 2001-06-29 16:31:22 from Thomas T. Veldhouse

pgsql-sql by date

Next:From: Thomas T. VeldhouseDate: 2001-06-29 16:31:22
Subject: Re:
Previous:From: Jeff EckermannDate: 2001-06-29 15:52:39
Subject: RE: Evaluating Subselects

pgsql-general by date

Next:From: Janning VygenDate: 2001-06-29 16:01:55
Subject: another stupid question: Database is being accessed by other users
Previous:From: Jan WieckDate: 2001-06-29 15:54:22
Subject: Re: function cache??

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