Re: Query performance

From: Erwin Brandstetter <brsaweda(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query performance
Date: 2006-05-28 22:38:55
Message-ID: b4712$447a2680$506d0da5$29295@news.chello.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Browse pgsql-performance by date

  From Date Subject
Next Message Greg Stark 2006-05-28 23:20:59 Re: App very unresponsive while performing simple update
Previous Message Erwin Brandstetter 2006-05-28 21:56:27 Re: Query performance