pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: Greg Stark <gsstark@mit.edu>
Cc: Christopher Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org, Jesse Vincent <jesse@bestpractical.com>
Subject: [ PROBLEM SOLVED ] Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
Date: Sat, 1 Nov 2003 11:17:02 +0530
Message-ID: <200311011117.02662.mallah@trade-india.com> (raw)
In-Reply-To: <87vfq6bbbn.fsf@stark.dyndns.tv>
References: <33298.203.145.130.142.1067457626.squirrel@mail.trade-india.com>
	<3FA34827.1030705@trade-india.com>
	<87vfq6bbbn.fsf@stark.dyndns.tv>



Well Sorry everyone ,

The problem was tracked down to a silly
datatype mismatch between two join columns
in table Groups(instance) and Tickets(id)
(int vs varchar )

7.4b5 is automatically taking care of this
mismatch hence it was getting executed there.

But , The problem is will this behaviour not 
allow to go such mistakes unnoticed?


Regards
Mallah.


On Friday 31 Oct 2003 4:08 am, Greg Stark wrote:
> 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@yahoo.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;




view thread (16+ messages)  latest in thread

Message-ID: <200311011117.02662.mallah@trade-india.com>
Permalink:  ../200311011117.02662.mallah@trade-india.com/
Also on:    postgresql.org/message-id/200311011117.02662.mallah@trade-india.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-performance@postgresql.org
  Cc: mallah@trade-india.com, gsstark@mit.edu, cbbrowne@acm.org, jesse@bestpractical.com
  Subject: Re: [ PROBLEM SOLVED ] Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
  In-Reply-To: <200311011117.02662.mallah@trade-india.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