Re: Using VIEW to simplify code... - Mailing list pgsql-sql

> If you want help, you must provide details.  The PG version number is
> relevant also.

Understood. PostgreSQL 7.2.3.

Here's the generated client side sql:

select   case when (hasflag(ut.flags,1) or (current_timestamp - '1 day'::interval <
trl.trans_date)) then     case when trl.trans_data like '%RNF'then       ' '   else     'Free'   end else   case when
trl.trans_datalike '%RNF' then      ' '   else     case when ct.misc_charge = '0'::money then        'Free'     else
  'View for ' || to_char(ct.misc_charge::float8,'FM$9990D90') || ''     end   end end as " ", 
 
trl.trans_date::timestamp(0) as "Date",  
tl.longtype as "Type",  
trl.trans_data as "Query Data",  
to_char(trl.trans_charge::float8,'FM$9990D90') as "Charged",  
trl.user_reference_id as "Reference ID"  
from trans_log_1 trl, addtypelong tl, user_table ut, company_table ct 
where  
((trl.username='myuser') and  
(trl.username=ut.username) and 
(ut.company_name=ct.company_name) and 
(trl.trans_date >= current_timestamp-'60 days'::interval) and  
(tl.shorttype=trl.trans_type) )  
union all  
select   case when (hasflag(ut.flags,16) or (current_timestamp - '1 day'::interval <
trl.trans_date)) then       case when trl.trans_data like '%RNF' then           ' '      else          'Free'      end
else     case when trl.trans_data like '%RNF' then           ' '      else          case when ct.misc_charge =
'0'::moneythen               'Free'          else              'View for ' ||
to_char(ct.misc_charge::float8,'FM$9990D90')|| ''          end      end  end as " ", 
 
trl.trans_date::timestamp(0) as "Date",  
tl.longtype as "Type",  
trl.trans_data as "Query Data",  
to_char(trl.trans_charge::float8,'FM$9990D90') as "Charged",  
trl.user_reference_id as "Reference ID"  
from trans_log_2 trl, addtypelong tl, user_table ut, company_table ct 
where 
((trl.username='myuser') and  (trl.username=ut.username) and (ut.company_name=ct.company_name) and (trl.trans_date >=
current_timestamp-'60days'::interval) and  (tl.shorttype=trl.trans_type) )  
 
union all  
select  case when trans_type = 'NS' then       ' '  else      case when (hasflag(ut.flags,16) or (current_timestamp -
'1day'::interval <
 
trl.trans_date)) then           case when trl.trans_data like '%RNF' then               ' '          else
'Free'         end      else          case when trl.trans_data like '%RNF' then               ' '          else
    case when ct.misc_charge = '0'::money then                   'Free'              else ' '              end
end     end  end as " ",  
 
trl.trans_date::timestamp(0) as "Date",  
case when trl.trans_type = 'NS' then 'Name' else 'Archive: ' || tl.longtype end
as "Type",  
trl.trans_data as "Query Data",  
to_char(trl.trans_charge::float8,'FM$9990D90') as "Charged",  
trl.user_reference_id as "Reference ID"  from trans_log_3 trl, addquerytype tl, user_table ut, company_table ct 
where 
((trl.username='myuser') and  (trl.username=ut.username) and (ut.company_name=ct.company_name) and (trl.trans_date >=
current_timestamp-'60days'::interval) and (tl.querytype=trl.trans_type) )  
 
union all 
select  case when (fdf is null or fdf='') then       ' '  else      'Free'  end as " ",  
trl.trans_date::timestamp(0) as "Date", 
'FORM: ' || trl.trans_type as "Type", 
trl.trans_data as "Query Data", 
to_char(trl.trans_charge, 'FM$9990D90') as "Charged", 
user_reference_id as "Reference ID" 
from trans_log_4 trl, user_table ut, company_table ct 
where 
((trl.username='myuser') and  (trl.username=ut.username) and (ut.company_name=ct.company_name) and (trl.trans_date >=
current_timestamp-'60days'::interval) ) 
 
order by 2 desc, 4 LIMIT 20 OFFSET 0;

Explain:

Limit  (cost=4339.83..4339.83 rows=20 width=158) ->  Sort  (cost=4339.83..4339.83 rows=285 width=158)       ->  Append
(cost=2477.60..4328.19rows=285 width=158)             ->  Subquery Scan *SELECT* 1  (cost=2477.60..2578.56 rows=187
 
width=157)                   ->  Hash Join  (cost=2477.60..2578.56 rows=187 width=157)                         ->  Seq
Scanon company_table ct  (cost=0.00..80.41
 
rows=1041 width=32)                         ->  Hash  (cost=2477.13..2477.13 rows=187 width=125)
      ->  Hash Join  (cost=287.56..2477.13 rows=187
 
width=125)                                     ->  Merge Join  (cost=286.31..2472.14
rows=187 width=102)                                           ->  Index Scan using
tl1_username_idx on trans_log_1 trl  (cost=0.00..2175.39 rows=187 width=59)
-> Sort  (cost=286.31..286.31
 
rows=3054 width=43)                                                 ->  Seq Scan on user_table ut(cost=0.00..109.54
rows=3054width=43)                                     ->  Hash  (cost=1.20..1.20 rows=20
 
width=23)                                           ->  Seq Scan on addtypelong tl 
(cost=0.00..1.20 rows=20 width=23)             ->  Subquery Scan *SELECT* 2  (cost=281.39..367.52 rows=16
width=151)                   ->  Hash Join  (cost=281.39..367.52 rows=16 width=151)                         ->  Hash
Join (cost=280.14..365.95 rows=16
 
width=128)                               ->  Seq Scan on company_table ct 
(cost=0.00..80.41 rows=1041 width=32)                               ->  Hash  (cost=280.10..280.10 rows=16
width=96)                                     ->  Nested Loop  (cost=0.00..280.10
rows=16 width=96)                                           ->  Index Scan using
tl2_username_idx on trans_log_2 trl  (cost=0.00..185.40 rows=16 width=53)                                           ->
IndexScan using
 
username_unique on user_table ut  (cost=0.00..5.94 rows=1 width=43)                         ->  Hash  (cost=1.20..1.20
rows=20width=23)                               ->  Seq Scan on addtypelong tl 
 
(cost=0.00..1.20 rows=20 width=23)             ->  Subquery Scan *SELECT* 3  (cost=306.69..393.32 rows=31
width=158)                   ->  Hash Join  (cost=306.69..393.32 rows=31 width=158)                         ->  Hash
Join (cost=305.53..391.53 rows=31
 
width=134)                               ->  Seq Scan on company_table ct 
(cost=0.00..80.41 rows=1041 width=32)                               ->  Hash  (cost=305.45..305.45 rows=31
width=102)                                     ->  Nested Loop  (cost=0.00..305.45
rows=31 width=102)                                           ->  Seq Scan on trans_log_3 trl 
(cost=0.00..120.01 rows=31 width=59)                                           ->  Index Scan using
username_unique on user_table ut  (cost=0.00..5.94 rows=1 width=43)                         ->  Hash  (cost=1.13..1.13
rows=13width=24)                               ->  Seq Scan on addquerytype tl 
 
(cost=0.00..1.13 rows=13 width=24)             ->  Subquery Scan *SELECT* 4  (cost=899.92..988.78 rows=51
width=154)                   ->  Hash Join  (cost=899.92..988.78 rows=51 width=154)                         ->  Seq
Scanon company_table ct  (cost=0.00..80.41
 
rows=1041 width=28)                         ->  Hash  (cost=899.80..899.80 rows=51 width=126)
   ->  Merge Join  (cost=286.31..899.80 rows=51
 
width=126)                                     ->  Index Scan using tl4_username_idx on
trans_log_4 trl  (cost=0.00..605.08 rows=51 width=87)



... which runs remarkably well ... you'd hate to see the code that generates
the sql.




Here's the view:
create view view_tl_table as
select 
trl.username as "username",
trl.trans_date::timestamp(0) as "trans_date",  
tl.longtype as "longtype",  
trl.trans_data as "trans_data",  
to_char(trl.trans_charge::float8,'FM$9990D90') as "trans_charge",  
trl.user_reference_id as "user_reference_id",
trl.trans_uuid as "trans_uuid",  -- Construct Link to retrieve Record...  case when trl.trans_data like '%RNF' then '�'
 else    case when (      hasflag(ut.flags,1) or       current_timestamp - '1 day'::interval < trl.trans_date or
ct.misc_charge= '0'::money      ) then         '<a href="dispatch.asp?user=' || trl.username || '&cb=' ||
 
current_timestamp ||         '&id=' || my_encode('addid','id=' || length(trl.trans_uuid) || 'S' ||
trl.trans_uuid) ||         '&date=' ||         trl.trans_date::timestamp(0) || '&type=' || tl.longtype ||
'">Free</a>'       else        '<a href="dispatch.asp?user=' || trl.username || '&cb=' ||
current_timestamp ||         '&id=' || my_encode('addid','id=' || length(trl.trans_uuid) || 'S' ||
trl.trans_uuid) || '&date=' ||         trl.trans_date::timestamp(0) || '&type=' || tl.longtype || '">View for
' ||         to_char(ct.misc_charge::float8,'FM$9990D90') || '</a>'       end   end as "link"
from trans_log_1 trl, addtypelong tl, user_table ut, company_table ct 
where (
(trl.username=ut.username) and 
(ut.company_name=ct.company_name) and 
(trl.trans_date >= current_timestamp-'60 days'::interval) and  
(tl.shorttype=trl.trans_type) )  
union all  
select  
trl.username,
trl.trans_date::timestamp(0),  
tl.longtype,  
trl.trans_data,  
to_char(trl.trans_charge::float8,'FM$9990D90'),  
trl.user_reference_id,
trl.trans_uuid,  -- Construct Link to retrieve Record...  case when trl.trans_data like '%RNF' then '�'   else    case
when(      hasflag(ut.flags,1) or       current_timestamp - '1 day'::interval < trl.trans_date or       ct.misc_charge
='0'::money      ) then         '<a href="dispatch.asp?user=' || trl.username || '&cb=' ||
 
current_timestamp ||         '&id=' || my_encode('addid','id=' || length(trl.trans_uuid) || 'S' ||
trl.trans_uuid) ||         '&date=' ||         trl.trans_date::timestamp(0) || '&type=' || tl.longtype ||
'">Free</a>'       else        '<a href="dispatch.asp?user=' || trl.username || '&cb=' ||
current_timestamp ||         '&id=' || my_encode('addid','id=' || length(trl.trans_uuid) || 'S' ||
trl.trans_uuid) || '&date=' ||         trl.trans_date::timestamp(0) || '&type=' || tl.longtype || '">View for
' ||         to_char(ct.misc_charge::float8,'FM$9990D90') || '</a>'       end   end 
from trans_log_2 trl, addtypelong tl, user_table ut, company_table ct 
where ((trl.username=ut.username) and (ut.company_name=ct.company_name) and (trl.trans_date >= current_timestamp-'60
days'::interval)and  (tl.shorttype=trl.trans_type) ) 
 
union all  
select 
trl.username, 
trl.trans_date::timestamp(0),  
case when trl.trans_type = 'NS' then 'Name' else 'Archive: ' || tl.longtype
end,  
trl.trans_data,
to_char(trl.trans_charge::float8,'FM$9990D90'),  
trl.user_reference_id,
null, case when trans_type = 'NS' or trl.trans_data like '%RNF' then    '�'  else    case when (
hasflag(ut.flags,1)or      current_timestamp - '1 day'::interval < trl.trans_date or      ct.misc_charge = '0'::money
  ) then          '<a href="dispatch.asp?user=' || trl.username || '&cb=' ||
 
current_timestamp ||         '&id=' || my_encode('addid','id=' || length(trl.id) || 'S' || trl.id)
||         '&date=' ||  trl.trans_date::timestamp(0) || '&type=' || tl.longtype
|| '">Free</a>'    end  end
from trans_log_3 trl, addquerytype tl, user_table ut, company_table ct 
where ((trl.username=ut.username) and (ut.company_name=ct.company_name) and (trl.trans_date >= current_timestamp-'60
days'::interval)and (tl.querytype=trl.trans_type) )  
 
union all 
select 
trl.username,
trl.trans_date::timestamp(0), 
'FORM: ' || trl.trans_type, 
trl.trans_data, 
to_char(trl.trans_charge, 'FM$9990D90'), 
user_reference_id,
null, case when (fdf is null or fdf='') then       '�'  else      '<a href="dispatch.asp?user=' || trl.username ||
'&cb='|| current_timestamp
 
|| '&date=' ||              trl.trans_date::timestamp(0) || '&type=form" target="_blank">Free</a>'  end
from trans_log_4 trl, user_table ut, company_table ct 
where ((trl.username=ut.username) and (ut.company_name=ct.company_name) and (trl.trans_date >= current_timestamp-'60
days'::interval)) 
 
order by 2 desc, 4;

explain select * from view_tl_table where username='myuser' limit 20 offset 0;
NOTICE:  QUERY PLAN:

Limit  (cost=97732.57..97732.57 rows=20 width=169) ->  Subquery Scan view_tl_table  (cost=97732.57..97732.57
rows=221099
width=169)       ->  Sort  (cost=97732.57..97732.57 rows=221099 width=169)             ->  Append
(cost=254.88..37205.21rows=221099 width=169)                   ->  Subquery Scan *SELECT* 1  (cost=254.88..30026.35
 
rows=188853 width=169)                         ->  Hash Join  (cost=254.88..30026.35 rows=188853
width=169)                               ->  Hash Join  (cost=253.63..26248.03
rows=188853 width=146)                                     ->  Seq Scan on trans_log_1 trl 
(cost=0.00..21745.20 rows=188853 width=71)                                     ->  Hash  (cost=246.00..246.00
rows=3054
width=75)                                           ->  Hash Join  (cost=83.01..246.00
rows=3054 width=75)                                                 ->  Seq Scan on user_table ut(cost=0.00..109.54
rows=3054width=43)                                                 ->  Hash  (cost=80.41..80.41
 
rows=1041 width=32)                                                       ->  Seq Scan on
company_table ct  (cost=0.00..80.41 rows=1041 width=32)                               ->  Hash  (cost=1.20..1.20
rows=20width=23)                                     ->  Seq Scan on addtypelong tl 
 
(cost=0.00..1.20 rows=20 width=23)                   ->  Subquery Scan *SELECT* 2  (cost=254.88..2155.90
rows=12312 width=167)                         ->  Hash Join  (cost=254.88..2155.90 rows=12312
width=167)                               ->  Hash Join  (cost=253.63..1908.40 rows=12312
width=144)                                     ->  Seq Scan on trans_log_2 trl 
(cost=0.00..1377.74 rows=12312 width=69)                                     ->  Hash  (cost=246.00..246.00 rows=3054
width=75)                                           ->  Hash Join  (cost=83.01..246.00
rows=3054 width=75)                                                 ->  Seq Scan on user_table ut(cost=0.00..109.54
rows=3054width=43)                                                 ->  Hash  (cost=80.41..80.41
 
rows=1041 width=32)                                                       ->  Seq Scan on
company_table ct  (cost=0.00..80.41 rows=1041 width=32)                               ->  Hash  (cost=1.20..1.20
rows=20width=23)                                     ->  Seq Scan on addtypelong tl 
 
(cost=0.00..1.20 rows=20 width=23)                   ->  Subquery Scan *SELECT* 3  (cost=240.60..419.72
rows=1126 width=154)                         ->  Hash Join  (cost=240.60..419.72 rows=1126
width=154)                               ->  Hash Join  (cost=157.59..317.00 rows=1126
width=122)                                     ->  Hash Join  (cost=1.16..135.24
rows=1126 width=79)                                           ->  Seq Scan on trans_log_3 trl 
(cost=0.00..111.56 rows=1126 width=55)                                           ->  Hash  (cost=1.13..1.13 rows=13
width=24)                                                 ->  Seq Scan on addquerytype
tl  (cost=0.00..1.13 rows=13 width=24)                                     ->  Hash  (cost=109.54..109.54 rows=3054
width=43)                                           ->  Seq Scan on user_table ut 
(cost=0.00..109.54 rows=3054 width=43)                               ->  Hash  (cost=80.41..80.41 rows=1041
width=32)                                     ->  Seq Scan on company_table ct 
(cost=0.00..80.41 rows=1041 width=32)                   ->  Subquery Scan *SELECT* 4  (cost=253.63..4603.25
rows=18808 width=154)                         ->  Hash Join  (cost=253.63..4603.25 rows=18808
width=154)                               ->  Seq Scan on trans_log_4 trl 
(cost=0.00..3973.46 rows=18808 width=87)                               ->  Hash  (cost=246.00..246.00 rows=3054
width=67)                                     ->  Hash Join  (cost=83.01..246.00
rows=3054 width=67)                                           ->  Seq Scan on user_table ut 
(cost=0.00..109.54 rows=3054 width=39)                                           ->  Hash  (cost=80.41..80.41
rows=1041 width=28)                                                 ->  Seq Scan on company_table
ct  (cost=0.00..80.41 rows=1041 width=28)

EXPLAIN

... And that's the whole thing! Phew! Ack!

CG


__________________________________________________
Do you Yahoo!?
Yahoo! Web Hosting - Let the expert host your site
http://webhosting.yahoo.com