Skip site navigation (1) Skip section navigation (2)

Re: query optimization differs between view and explicit

From: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>
To: Reece Hart <reece(at)in-machina(dot)com>
Cc: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: query optimization differs between view and explicit
Date: 2004-01-30 00:52:15
Message-ID: 4019AABF.3030405@familyhealth.com.au (view raw or flat)
Thread:
Lists: pgsql-performance
> rkh(at)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

In response to

pgsql-performance by date

Next:From: Bill MoranDate: 2004-01-30 01:07:40
Subject: Re: [PERFORM] Set-Returning Functions WAS: On the performance of
Previous:From: Reece HartDate: 2004-01-30 00:31:39
Subject: query optimization differs between view and explicit query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group