pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Thomas T. Veldhouse <veldy@veldy.net>
To: tamsin <tg_mail@bryncadfan.co.uk>
To: PostgreSQL-general <pgsql-general@postgreSQL.org>
Subject: Re:
Date: Fri, 29 Jun 2001 11:31:22 -0500
Message-ID: <009d01c100b8$ef4b8190$3028680a@tgt.com> (raw)
References: <NEBBKHBOBMJCHDMGKCNJGEFFDAAA.tg_mail@bryncadfan.co.uk>
	<003501c100b4$4ea2cd10$3028680a@tgt.com>

Heck, you could refine it this way as well.

select pm.*
from purchasemaster pm
  inner join itemmaster im
    on (pm.transactionid = im.transactionid and im.itempriority = 2)


This might reduce memory usage.  I am not sure which will turn out to perform better in a resource friendly environment.

Tom Veldhouse
veldy@veldy.net

PS  There is a lot of HTML email on this list.  Is this generally considered good?  Or should I convert my replies to text?


----- Original Message ----- 
  From: Thomas T. Veldhouse 
  To: tamsin ; PostgreSQL-general 
  Sent: Friday, June 29, 2001 10:58 AM
  Subject: Re: [GENERAL] 


  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
  veldy@veldy.net

    ----- Original Message ----- 
    From: tamsin 
    To: pgsql-general@postgresql.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,
    tamsin

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


      Hi,

      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,
      Kapil

view thread (7+ messages)  latest in thread

Message-ID: <009d01c100b8$ef4b8190$3028680a@tgt.com>
Permalink:  ../009d01c100b8$ef4b8190$3028680a@tgt.com/
Also on:    postgresql.org/message-id/009d01c100b8$ef4b8190$3028680a@tgt.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: veldy@veldy.net, tg_mail@bryncadfan.co.uk, pgsql-general@postgreSQL.org
  Subject: Re:
  In-Reply-To: <009d01c100b8$ef4b8190$3028680a@tgt.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox