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

Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Rajesh Kumar Mallah <mallah(at)trade-india(dot)com>
Cc: Greg Stark <gsstark(at)mit(dot)edu>,Christopher Browne <cbbrowne(at)acm(dot)org>,pgsql-performance(at)postgresql(dot)org,Jesse Vincent <jesse(at)bestpractical(dot)com>
Subject: Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
Date: 2003-10-30 22:38:36
Message-ID: 87vfq6bbbn.fsf@stark.dyndns.tv (view raw or flat)
Thread:
Lists: pgsql-performance
Well, you might want to try the EXISTS version. I'm not sure if it'll be
faster or slower though. In theory it should be the same.

Hum, I didn't realize the principals table was the largest table. But Postgres
knew that so one would expect it to have found a better plan. The IN/EXISTS
handling was recently much improved but perhaps there's still room :)

SELECT *
  FROM tickets
 WHERE EXISTS (
       SELECT 1
         FROM groups
         JOIN principals ON (groups.id = principals.objectid)
         JOIN cachedgroupmembers ON (principals.id = cachedgroupmembers.groupid)
         JOIN users ON (cachedgroupmembers.memberid = users.id)
        WHERE lower(users.emailaddress) = 'mallah_rajesh(at)yahoo(dot)com'
          AND groups.domain = 'RT::Ticket-Role'
          AND groups.type   = 'Requestor'
          AND principals.principaltype = 'group'
          AND groups.instance = tickets.id
  )
  AND type = 'ticket'
  AND effectiveid = tickets.id
  AND (status = 'new' OR status = 'open')
ORDER BY priority DESC
LIMIT 10;

-- 
greg


In response to

Responses

pgsql-performance by date

Next:From: Christopher Kings-LynneDate: 2003-10-31 01:38:12
Subject: Re: vacuum locking
Previous:From: Nathan ScottDate: 2003-10-30 22:12:21
Subject: Re: [linux-lvm] RE: [PERFORM] backup/restore - another ar ea.

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