agustinvinao

72483	1
+-72480	2
+---72549	3
+-----102279	4
+-------72481	5
+---------72668	6
+-72496	2
+-72668	2
+-84940	2
+---72549	3

agustinvinao

~% ll /usr/local/Cellar/postgresql/9.0.2/share/contrib 
total 272
-rw-r--r--  1 agustinvinao  staff  10746 Sep 28  2011 _int.sql
-rw-r--r--  1 agustinvinao  staff   1548 Sep 28  2011 adminpack.sql
-rw-r--r--  1 agustinvinao  staff   5980 Sep 28  2011 dblink.sql
-rw-r--r--  1 agustinvinao  staff   1267 Sep 28  2011 fuzzystrmatch.sql
-rw-r--r--  1 agustinvinao  staff  12932 Sep 28  2011 hstore.sql
-rw-r--r--  1 agustinvinao  staff    742 Sep 28  2011 lo.sql
-rw-r--r--  1 agustinvinao  staff    779 Sep 28  2011 pg_buffercache.sql
-rw-r--r--  1 agustinvinao  staff   3809 Sep 28  2011 pg_trgm.sql

agustinvinao

SELECT t.*, subs.id FROM connectby('subs', 'sub_id', 'advisor_id', '72483', 0) AS t(sub_id int, advisor_id int, level int), subs WHERE t.sub_id = subs.sub_id AND t.advisor_id = subs.advisor_id

results:
sub     advisor level   id
72668	72483	1	1680
=> 72480	72483	1	1778
72549	72480	2	1779
102279	72549	3	1951
72481	102279	4	1884
72668	72481	5	1885

agustinvinao

select a_addresses.id, 
	--BOOL_AND(
		(
			pt_addresses.id = protected_territories.address_id AND 
			(
				ACOS(
					SIN(RADIANS(a_addresses.latitude)) * 
					SIN(RADIANS(pt_addresses.latitude)) + 
					COS(RADIANS(a_addresses.latitude)) * 
					COS(RADIANS(pt_addresses.latitude)) * 

agustinvinao

[{"sub_id"=>"72668", "advisor_id"=>"72481", "level"=>"5", "id"=>"1885"}, 
            {"sub_id"=>"72668", "advisor_id"=>"72481", "level"=>"5", "id"=>"1885"}, 
            {"sub_id"=>"72481", "advisor_id"=>"102279", "level"=>"4", "id"=>"1884"}, 
            {"sub_id"=>"72481", "advisor_id"=>"102279", "level"=>"4", "id"=>"1884"}, 
            {"sub_id"=>"102279", "advisor_id"=>"72549", "level"=>"3", "id"=>"1951"}, 
            {"sub_id"=>"102279", "advisor_id"=>"72549", "level"=>"3", "id"=>"1951"}, 
            {"sub_id"=>"72549", "advisor_id"=>"72480", "level"=>"2", "id"=>"1779"}, 
            {"sub_id"=>"72549", "advisor_id"=>"84940", "level"=>"2", "id"=>"1830"}, 
            {"sub_id"=>"92808", "advisor_id"=>"72483", "level"=>"1", "id"=>"1952"}, 
            {"sub_id"=>"72480", "advisor_id"=>"72483", "level"=>"1", "id"=>"1778"}, 

agustinvinao

72483	
+-72480	
+---72549	
+-----102279	
+-------72481	
+---------72668	
+-72496	
+-72668	
+-84940	
+---72549

agustinvinao

<ul>
	<li>
		ad1 
		<ul>
			<li>sub1</li>
		</ul>
	</li>
	<li>
		ad2
	</li>

agustinvinao

SELECT 
	COALESCE(SUM(CASE submitted_premiums.kind WHEN 'Target' THEN submitted_premiums.premium * (policy_advisors.commission_percent / 100) ELSE 0.00 END), 0.00) AS target_premium, 
	COALESCE(SUM(CASE submitted_premiums.kind WHEN 'Single' THEN submitted_premiums.premium * (policy_advisors.commission_percent / 100) ELSE 0.00 END), 0.00) AS single_premium, 
	COALESCE(SUM(CASE submitted_premiums.kind WHEN 'Excess' THEN submitted_premiums.premium * (policy_advisors.commission_percent / 100) ELSE 0.00 END), 0.00) AS excess_premium, 
	advisors.id AS entity_id, 
	TRIM(CASE WHEN people.first_name <> ' ' AND people.first_name IS NOT NULL THEN people.first_name || ' ' ELSE '' END::Text || 
	CASE WHEN people.middle_name <> ' ' AND people.middle_name IS NOT NULL THEN people.middle_name || ' ' ELSE '' END::Text || 
	CASE WHEN people.last_name <> ' ' AND people.last_name IS NOT NULL THEN people.last_name || ' ' ELSE '' END::Text || 
	CASE WHEN people.suffix <> ' ' AND people.suffix IS NOT NULL THEN people.suffix ELSE '' 

agustinvinao

SELECT 
--	COALESCE(SUM(CASE submitted_premiums.kind WHEN 'Target' THEN submitted_premiums.premium * (policy_advisors.commission_percent / 100) ELSE 0.00 END), 0.00) AS target_premium, 
--	COALESCE(SUM(CASE submitted_premiums.kind WHEN 'Single' THEN submitted_premiums.premium * (policy_advisors.commission_percent / 100) ELSE 0.00 END), 0.00) AS single_premium, 
--	COALESCE(SUM(CASE submitted_premiums.kind WHEN 'Excess' THEN submitted_premiums.premium * (policy_advisors.commission_percent / 100) ELSE 0.00 END), 0.00) AS excess_premium, 
	COALESCE(SUM(CASE advisor_submitted_premium_totals.kind WHEN 'Target' THEN advisor_submitted_premium_totals.premium ELSE 0.00 END), 0.00) AS target_premium, 
	COALESCE(SUM(CASE advisor_submitted_premium_totals.kind WHEN 'Single' THEN advisor_submitted_premium_totals.premium ELSE 0.00 END), 0.00) AS single_premium, 
	COALESCE(SUM(CASE advisor_submitted_premium_totals.kind WHEN 'Excess' THEN advisor_submitted_premium_totals.premium ELSE 0.00 END), 0.00) AS excess_premium, 
	advi

agustinvinao

# And with a simple call like AdvisorSubmittedPremiumTotal.first, these are the results (1s or 1.2s) :
>> AdvisorSubmittedPremiumTotal.first
  AdvisorSubmittedPremiumTotal Load (1206.7ms)  SELECT "advisor_submitted_premium_totals".* FROM "advisor_submitted_premium_totals" LIMIT 1
=> #<AdvisorSubmittedPremiumTotal policy_status_id: 1, advisor_id: 46, date: "2008-05-13", kind: "Initial", premium: #<BigDecimal:106f4b1c0,'0.145237E4',18(36)>>

# If I filter the date with the current year I have better performance but is slowest than the original sql inside reports (avg 0.5s):
>> AdvisorSubmittedPremiumTotal.where("date>=? and date<=?",date_start, date_end).first
  AdvisorSubmittedPremiumTotal Load (456.5ms)  SELECT "advisor_submitted_premium_totals".* FROM "advisor_submitted_premium_totals" WHERE (date>='2012-01-01' and date<='2012-12-31') LIMIT 1
=> #<AdvisorSubmittedPremiumTotal policy_status_id: 1, advisor_id: 54, date: "2012-01-04", kind: "Initial", premium: #<BigDecimal:108c77cd0,'0.975E3',9(36)>>
>> Advisor