agora inbox for pgsql-performance@postgresql.org  

help / color / mirror / Atom feed

From: Erwin Brandstetter <brsaweda@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Query performance
Date: Mon, 29 May 2006 00:38:55 +0200
Message-ID: <b4712$447a2680$506d0da5$29295@news.chello.at> (raw)
In-Reply-To: <e4s9gh$s5s$1@newsreader1.utanet.at>
References: <e4rvnb$o8f$1@newsreader1.utanet.at>
	<1148294589.715695@proxy.dienste.wien.at>
	<e4s9gh$s5s$1@newsreader1.utanet.at>

Antonio Batovanja wrote:
(...)

> 1) the slooooow query:
> EXPLAIN ANALYZE SELECT DISTINCT ldap_entries.id, organization.id,
> text('organization') AS objectClass, ldap_entries.dn AS dn FROM
> ldap_entries, organization, ldap_entry_objclasses WHERE
> organization.id=ldap_entries.keyval AND ldap_entries.oc_map_id=1 AND
> upper(ldap_entries.dn) LIKE '%DC=HUMANOMED,DC=AT' AND 1=1 OR
> (ldap_entries.id=ldap_entry_objclasses.entry_id AND
> ldap_entry_objclasses.oc_name='organization');


First, presenting your query in any readable form might be helpful if 
you want the community to help you. (Hint! Hint!)

SELECT DISTINCT ldap_entries.id, organization.id,
	text('organization') AS objectClass, ldap_entries.dn AS dn
   FROM ldap_entries, organization, ldap_entry_objclasses
  WHERE organization.id=ldap_entries.keyval
    AND ldap_entries.oc_map_id=1
    AND upper(ldap_entries.dn) LIKE '%DC=HUMANOMED,DC=AT'
    AND 1=1
    OR (ldap_entries.id=ldap_entry_objclasses.entry_id
    AND ldap_entry_objclasses.oc_name='organization');

Next, you might want to use aliases to make it more readable.

SELECT DISTINCT e.id, o.id, text('organization') AS objectClass, e.dn AS dn
   FROM ldap_entries AS e, organization AS o, ldap_entry_objclasses AS eo
  WHERE o.id=e.keyval
    AND e.oc_map_id=1
    AND upper(e.dn) LIKE '%DC=HUMANOMED,DC=AT'
    AND 1=1
    OR (e.id=eo.entry_id
    AND eo.oc_name='organization');

There are a couple redundant (nonsensical) items, syntax-wise. Let's 
strip these:

SELECT DISTINCT e.id, o.id, text('organization') AS objectClass, e.dn
   FROM ldap_entries AS e, organization AS o, ldap_entry_objclasses AS eo
  WHERE o.id=e.keyval
    AND e.oc_map_id=1
    AND e.dn ILIKE '%DC=HUMANOMED,DC=AT'
     OR e.id=eo.entry_id
    AND eo.oc_name='organization';

And finally, I suspect the lexical precedence of AND and OR might be the 
issue here. 
http://www.postgresql.org/docs/8.1/static/sql-syntax.html#SQL-PRECEDENCE
Maybe that is what you really want (just guessing):

SELECT DISTINCT e.id, o.id, text('organization') AS objectClass, e.dn
   FROM ldap_entries e
   JOIN organization o ON o.id=e.keyval
   LEFT JOIN ldap_entry_objclasses eo ON eo.entry_id=e.id
  WHERE e.oc_map_id=1
    AND e.dn ILIKE '%DC=HUMANOMED,DC=AT'
     OR eo.oc_name='organization)';

I didn't take the time to read the rest. My appologies if I guessed wrong.


Regards, Erwin

view thread (48+ messages)  latest in thread

Message-ID: <b4712$447a2680$506d0da5$29295@news.chello.at>
Permalink:  ../b4712$447a2680$506d0da5$29295@news.chello.at/
Also on:    postgresql.org/message-id/b4712$447a2680$506d0da5$29295@news.chello.at

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: brsaweda@gmail.com
  Subject: Re: Query performance
  In-Reply-To: <b4712$447a2680$506d0da5$29295@news.chello.at>

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

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