Re: query optimization differs between view and explicit

> rkh@csb-dev=> create view v1 as
> select distinct on (AH.p2gblataln_id) AH.p2gblataln_id,H.pseq_id,min(H.pstart) as 
> "pstart",
> max(H.pstop) as "pstop",A.ident,(A.ident/Q.len::float*100)::int as "pct_ident",
> sum(H.pstop-H.pstart+1) as "aln_length",H.genasm_id,H.chr,H.plus_strand,min(H.gstart) as 
> "gstart",
> max(H.gstop) as "gstop"
> from p2gblathsp H
> join p2gblatalnhsp AH on H.p2gblathsp_id=AH.p2gblathsp_id
> join p2gblataln A on AH.p2gblataln_id=A.p2gblataln_id
> join pseq Q on H.pseq_id=Q.pseq_id
> group by 
> AH.p2gblataln_id,H.pseq_id,H.genasm_id,H.chr,H.plus_strand,A.ident,Q.len;
> CREATE VIEW
> Time: 103.041 ms

What happens if you make it a function:

CREATE FUNCTION f1() RETURNS ... AS '
select distinct on (AH.p2gblataln_id) 
AH.p2gblataln_id,H.pseq_id,min(H.pstart) as "pstart",
max(H.pstop) as "pstop",A.ident,(A.ident/Q.len::float*100)::int as 
"pct_ident",
sum(H.pstop-H.pstart+1) as 
"aln_length",H.genasm_id,H.chr,H.plus_strand,min(H.gstart) as "gstart",
max(H.gstop) as "gstop"
from p2gblathsp H
join p2gblatalnhsp AH on H.p2gblathsp_id=AH.p2gblathsp_id
join p2gblataln A on AH.p2gblataln_id=A.p2gblataln_id
join pseq Q on H.pseq_id=Q.pseq_id
where H.pseq_id=76
group by 
AH.p2gblataln_id,H.pseq_id,H.genasm_id,H.chr,H.plus_strand,A.ident,Q.len
' LANGUAGE SQL;

I suspect that will be even faster than the normal (non-view) query.

Chris

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx