Re: How to optimize this query ? - Mailing list pgsql-sql

<p align="LEFT"><span lang="en-us"><font face="Courier New" size="2">Actually,</font></span><span lang="en-us"> <font
face="CourierNew" size="2">I have got another query where I need to remove the subqueries ...</font></span><span
lang="en-us"><fontface="Courier New" size="2"> It is almost the same query but this time, I don't search in the table
LEA</font></span><spanlang="en-us"><font face="Courier New" size="2">D</font></span><span lang="en-us"><font
face="CourierNew" size="2"> but in the table</font></span><span lang="en-us"> <font face="Courier New"
size="2">MEMBER</font></span><spanlang="en-us"> <font face="Courier New" size="2">…</font></span><span
lang="en-us"></span><palign="LEFT"><span lang="en-us"><font face="Courier New" size="2">HERE are the
tables:</font></span><palign="LEFT"><span lang="en-us"><font face="Courier New" size="2"></font></span><span
lang="en-us"> <fontface="Courier New" size="2">affiliate_lockout  (6 rows)  (member_id,
affiliate_id)</font></span><spanlang="en-us"></span><p align="LEFT"><span lang="en-us"><font face="Courier New"
size="2"> automated     (4 rows)        (member_id,</font></span><span lang="en-us"> <font face="Courier New"
size="2">…</font></span><spanlang="en-us"><font face="Courier New" size="2">)</font></span><span lang="en-us"></span><p
align="LEFT"><spanlang="en-us"><font face="Courier New" size="2"> lead   (4490 rows)           (id,
...)</font></span><palign="LEFT"><span lang="en-us"><font face="Courier New" size="2"> member  (6 rows)            
(id,...)</font></span><p align="LEFT"><span lang="en-us"><font face="Courier New" size="2"> member_exclusion (3
rows)   (member_id, member_id_to_exclude)</font></span><p align="LEFT"><span lang="en-us"><font face="Courier New"
size="2"></font> <fontface="Courier New" size="2">purchase (10000 rows)        (lead_id, member_id, ...</font><font
face="CourierNew" size="2">)</font></span><br /><p align="LEFT"><span lang="en-us"><font color="#000000" face="Courier
New"size="1">----------------------------</font></span><p align="LEFT"><span lang="en-us"><font color="#000000"
face="CourierNew" size="1">select member.id, automated.delivery, member.email </font></span><p align="LEFT"><span
lang="en-us"><fontcolor="#000000" face="Courier New" size="1"> </font></span><p align="LEFT"><span lang="en-us"><font
color="#000000"face="Courier New" size="1">from (automated INNER JOIN member ON</font> <font color="#000000"
face="CourierNew" size="1">member.id = automated.member_id)</font></span><p align="LEFT"><span lang="en-us"><font
color="#000000"face="Courier New" size="1"> </font></span><p align="LEFT"><span lang="en-us"><font color="#000000"
face="CourierNew" size="1">where activated=1</font></span><p align="LEFT"><span lang="en-us"><font color="#000000"
face="CourierNew" size="1">  and website='$SITE_NAME'</font></span><p align="LEFT"><span lang="en-us"><font
color="#000000"face="Courier New" size="1">  and (select count(*) from trans_member where
(unix_timestamp(now())-unix_timestamp(date))<</font></span><span lang="en-us"></span><span lang="en-us"><font
color="#000000"face="Courier New" size="1"></font></span><span lang="en-us"></span><span lang="en-us"> <font
color="#000000"face="Courier New" size="1">(unix_timestamp(now())-'$today_midnight') and type='purchase' and comment
LIKE</font><fontcolor="#000000" face="Courier New" size="1"> '%automated%' </font></span><p align="LEFT"><span
lang="en-us"><fontcolor="#000000" face="Courier New" size="1">  and member_id=member.id and comment LIKE '%$type%')
<max_$field </font></span><p align="LEFT"><span lang="en-us"><font color="#000000" face="Courier New" size="1">  and
balance_in_points> $price </font></span><p align="LEFT"><span lang="en-us"><font color="#000000" face="Courier New"
size="1"> and credit_ratings_t$n LIKE '%$lead[borrower_credit_rating]%' </font></span><p align="LEFT"><span
lang="en-us"><fontcolor="#000000" face="Courier New" size="1">  and states LIKE '%$lead[prop_state]%' </font></span><p
align="LEFT"><spanlang="en-us"><font color="#000000" face="Courier New" size="1">  and ltv_t$n/100 >=
(cast(</font><fontcolor="#000000" face="Courier New" size="1">$lead[loan_amount] as unsigned) /
cast($lead[current_value]as unsigned)) </font></span><p align="LEFT"><span lang="en-us"><font color="#000000"
face="CourierNew" size="1">  and amount_t$n < $lead[loan_amount] </font></span><p align="LEFT"><span
lang="en-us"><fontcolor="#000000" face="Courier New" size="1"> </font></span><p align="LEFT"><span lang="en-us"><font
color="#000000"face="Courier New" size="1">  AND $id NOT IN (select lead_id from purchase where member_id=member.id)
</font></span><palign="LEFT"><span lang="en-us"><font color="#000000" face="Courier New" size="1">  AND $aff_id NOT IN
(selectaffiliate_locked_id from affiliate_lockout where member_id=member.id) </font></span><p align="LEFT"><span
lang="en-us"><fontcolor="#000000" face="Courier New" size="1">  AND $id NOT IN (select lead_id from purchase where
member_idIN (select member_id_to_exclude from member_exclusion where member_id=member.id))</font></span><p
align="LEFT"><spanlang="en-us"><font color="#000000" face="Courier New" size="1"> </font></span><p align="LEFT"><span
lang="en-us"><fontcolor="#000000" face="Courier New" size="1">ORDER</font> <font color="#000000" face="Courier New"
size="1">BYbalance_in_points DESC</font></span><p align="LEFT"><span lang="en-us"><font color="#000000" face="Courier
New"size="2">--------------------</font></span><span lang="en-us"></span><p align="LEFT"><span lang="en-us"><font
color="#000000"face="Courier New" size="2"> </font></span><p align="LEFT"><span lang="en-us"><font color="#000000"
face="CourierNew" size="2">For this one, I really don</font><font color="#000000" face="Courier New"
size="2">’</font><fontcolor="#000000" face="Courier New" size="2">t know how to remove the 3 subqueries at the end
becausethe $id and $aff_id are values external to the query, and there is no table to join …</font></span><p
align="LEFT"><spanlang="en-us"><font color="#000000" face="Courier New" size="2"> </font></span><p align="LEFT"><span
lang="en-us"><fontcolor="#000000" face="Courier New" size="2">I tried to remove the subqueries and to rewrite
them</font><fontcolor="#000000" face="Courier New" size="2"> to 3 small external queries that I run for each result
givenby this first query, but I guess this will be much longer if the tables are big …</font></span><span
lang="en-us"></span><palign="LEFT"><span lang="en-us"><font color="#000000" face="Courier New" size="2">What do you
thinkabout ?</font></span><span lang="en-us"></span>