> 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