[Date Prev][Date Next] [Chronological] [Thread] [Top]

Re: proxy-cache

Pierangelo Masarati wrote:

There were no changes between 2.2.15 and 2.2.17; there are lots of changes
between 2.2.17 and HEAD; at a first glance there's not much that should
impact performances.  There's a small issue, I had to change the way
schema mapping is looked up; at some point it was done by comparing
pointers, but that made tests not portable because attribute ordering in
the results was machine or even run-time dependent; so I made it default
to strcmp based.  I haven't noticed significant performance impact, but I
never used very large databases.  You can revert to the old behavior by
recompiling back-sql (actually, all you need to recompile is schema-map.c)
with -DBACKSQL_USE_PTR_CMP (see the comment at the beginning of
schema-map.c).

This may (and will) cause sql-test* fail because the results are not
ordered as in the reference.

Anybody please holler if you find a better way to sort results in a better
way...

p. 

Hi,

I've done a bit of digging and it seems the difference is in the way the candidate list is constructed. The new query is much much slower than the new one (by a large amount), I guess that the (maybe overly redundant) phrase 'ldap_entries.id=ldap_entry_objclasses.entry_id and' helps the query optimizer in postgres a lot....

If it doesn't make any differnce to you, I guess I liked the performance of the old query a lot better.

(new version)
Constructed query: SELECT DISTINCT ldap_entries.id,posixshadowaccount.id,text('account') AS objectClass,ldap_entries.dn AS dn FROM ldap_entries,posixshadowaccount,ldap_entry_objclasses WHERE posixshadowaccount.id=ldap_entries.keyval AND ldap_entries.oc_map_id=? AND upper(ldap_entries.dn) LIKE ? AND ldap_entries.keyval=posixshadowaccount.id AND ldap_entries.oc_map_id=3 AND (ldap_entry_objclasses.oc_name='posixAccount')
id: '3'
(sub)dn: "%OU=TERAS,O=SARA,C=NL"


(old version)
Constructed query: SELECT DISTINCT ldap_entries.id,posixshadowaccount.id,text('account') AS objectClass,ldap_entries.dn AS dn FROM ldap_entries,posixshadowaccount,ldap_entry_objclasses WHERE posixshadowaccount.id=ldap_entries.keyval AND ldap_entries.oc_map_id=? AND upper(ldap_entries.dn) LIKE ? AND ldap_entries.id=ldap_entry_objclasses.entry_id and ldap_entries.keyval=posixshadowaccount.id AND ldap_entries.oc_map_id=3 AND (ldap_entry_objclasses.oc_name='posixAccount')
id: '3'
(sub)dn: "%OU=TERAS,O=SARA,C=NL"

explain output for both queries:

db2=> explain
db2-> SELECT DISTINCT ldap_entries.id,posixshadowaccount.id,text('account') AS objectClass,ldap_entries.dn AS dn FROM ldap_entries,posixshadowaccount,ldap_entry_objclasses WHERE posixshadowaccount.id=ldap_entries.keyval AND ldap_entries.oc_map_id=3 AND upper(ldap_entries.dn) LIKE '%OU=TERAS,O=SARA,C=NL' AND ldap_entries.keyval=posixshadowaccount.id AND ldap_entries.oc_map_id=3 AND (ldap_entry_objclasses.oc_name='posixAccount')
db2-> ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=182.39..184.30 rows=153 width=40)
-> Sort (cost=182.39..182.77 rows=153 width=40)
Sort Key: ldap_entries.id, m.id, 'account'::text, ldap_entries.dn
-> Nested Loop (cost=115.35..176.84 rows=153 width=40)
-> Index Scan using ldap_entry_objclasses_name_key on ldap_entry_objclasses (cost=0.00..58.43 rows=17 width=0)
Index Cond: (oc_name = 'posixAccount'::text)
-> Materialize (cost=115.35..115.44 rows=9 width=40)
-> Nested Loop (cost=0.00..115.35 rows=9 width=40)
-> Nested Loop (cost=0.00..61.30 rows=9 width=52)
-> Index Scan using ldap_entries_oc_keyval_key on ldap_entries (cost=0.00..32.56 rows=1 width=40)
Index Cond: (oc_map_id = 3)
Filter: (upper(dn) ~~ '%OU=TERAS,O=SARA,C=NL'::text)
-> Index Scan using user_machine_login_key on user_machine_login m (cost=0.00..28.64 rows=8 width=16)
Index Cond: (m.id = "outer".keyval)
-> Index Scan using uid_uniek on user_global g (cost=0.00..5.99 rows=1 width=12)
Index Cond: ("outer".userid = g.userid)
(16 rows)

db2=> explain
db2-> SELECT DISTINCT ldap_entries.id,posixshadowaccount.id,text('account') AS objectClass,ldap_entries.dn AS dn FROM ldap_entries,posixshadowaccount,ldap_entry_objclasses WHERE posixshadowaccount.id=ldap_entries.keyval AND ldap_entries.oc_map_id=3 AND upper(ldap_entries.dn) LIKE '%OU=TERAS,O=SARA,C=NL' AND ldap_entries.id=ldap_entry_objclasses.entry_id and ldap_entries.keyval=posixshadowaccount.id AND ldap_entries.oc_map_id=3 AND (ldap_entry_objclasses.oc_name='posixAccount');
QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------
Unique (cost=173.97..174.09 rows=9 width=40)
-> Sort (cost=173.97..174.00 rows=9 width=40)
Sort Key: ldap_entries.id, m.id, 'account'::text, ldap_entries.dn
-> Nested Loop (cost=0.00..173.83 rows=9 width=40)
-> Nested Loop (cost=0.00..119.79 rows=9 width=52)
-> Nested Loop (cost=0.00..91.05 rows=1 width=40)
-> Index Scan using ldap_entries_oc_keyval_key on ldap_entries (cost=0.00..32.56 rows=1 width=40)
Index Cond: (oc_map_id = 3)
Filter: (upper(dn) ~~ '%OU=TERAS,O=SARA,C=NL'::text)
-> Index Scan using ldap_entry_id_key on ldap_entry_objclasses (cost=0.00..58.47 rows=1 width=4)
Index Cond: ("outer".id = ldap_entry_objclasses.entry_id)
Filter: (oc_name = 'posixAccount'::text)
-> Index Scan using user_machine_login_key on user_machine_login m (cost=0.00..28.64 rows=8 width=16)
Index Cond: (m.id = "outer".keyval)
-> Index Scan using uid_uniek on user_global g (cost=0.00..5.99 rows=1 width=12)
Index Cond: ("outer".userid = g.userid)
(16 rows)


--
Met vriendelijke groeten,

Remco Post

SARA - Reken- en Netwerkdiensten                      http://www.sara.nl
High Performance Computing  Tel. +31 20 592 3000    Fax. +31 20 668 3167

"I really didn't foresee the Internet. But then, neither did the
computer industry. Not that that tells us very much of course - the
computer industry didn't even foresee that the century was going to
end." -- Douglas Adams