Re: Using VIEW to simplify code...

> 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_data like '%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'::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_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-'60 days'::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 - '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'::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-'60 days'::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-'60 days'::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.19 rows=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 Scan on 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=3054 width=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)
-> Index Scan using
username_unique on user_table ut (cost=0.00..5.94 rows=1 width=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* 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=13 width=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 Scan on 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.21 rows=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=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)
-> 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=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=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)
-> 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* 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