I have three queries that are essentially identical. Two of them run in fractions of a second; one of them takes longer than 15 minutes to return. (Not sure how long it totally takes, that has been the limit of my patience.)
The only difference between these queries is the use of table aliases in the sql query. What is happening in postgres that this makes such a difference?
SQL queries below.
Thanks,
VanL
FAST (0.017 second):
select
mm_batch.name as batch_name,
mm_domain.name as domain_name,
mm_management_unit.name as management_unit_name,
mm_customer.firstname as customer_name,
mm_legacy_account.username as old_username,
mm_target_account.username as new_username
from
mm_batch,
mm_domain,
mm_management_unit,
mm_customer,
mm_legacy_account,
mm_target_account
where
mm_domain.bid = mm_batch.id
and mm_domain.mid = mm_management_unit.id
and mm_domain.cid = mm_customer.id
and mm_domain.lid = mm_legacy_account.id
and mm_domain.tid = mm_target_account.id
and mm_domain.name = 'example.com';
========================================================
FAST (0.016 second):
select
B.name as batch_name,
D.name as domain_name,
M.name as management_unit_name,
C.firstname as customer_name,
LA.username as old_username,
TA.username as new_username
from
mm_batch B,
mm_domain D,
mm_management_unit M,
mm_customer C,
mm_legacy_account LA,
mm_target_account TA
where
D.bid = B.id
and D.mid = M.id
and D.cid = C.id
and D.lid = LA.id
and D.tid = TA.id
and D.name = 'example.com';
=========================================================
SLOW ( > 15 minutes):
select
mm_batch.name as batch_name,
mm_domain.name as domain_name,
mm_management_unit.name as management_unit_name,
mm_customer.firstname as customer_name,
mm_legacy_account.username as old_username,
mm_target_account.username as new_username
from
mm_batch B,
mm_domain D,
mm_management_unit M,
mm_customer C,
mm_legacy_account LA,
mm_target_account TA
where
mm_domain.bid = mm_batch.id
and mm_domain.mid = mm_management_unit.id
and mm_domain.cid = mm_customer.id
and mm_domain.lid = mm_legacy_account.id
and mm_domain.tid = mm_target_account.id
and mm_domain.name = 'example.com';---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
