On 1 July 2010 06:19, Srikanth Kata <srikanth@xxxxxxxxxxxx> wrote: > > Please tell me What is the best way to optimize this query > > select > s.*,a.actid,a.phone,d.domid,d.domname,d.domno,a.actno,a.actname,p.descr > as svcdescr from vwsubsmin s inner join packages p on s.svcno=p.pkgno inner > join > account a on a.actno=s.actno inner join ssgdom d on a.domno=d.domno inner > join (select subsno from > getexpiringsubs($1,cast($2 as integer),cast($3 as double precision), $4) as > (subsno int,expirydt timestamp without time zone,balcpt double precision)) > as e on s.subsno=e.subsno where s.status <=15 and d.domno=$5 order by > d.domname,s.expirydt,a.actname > -- > View this message in context: http://old.nabble.com/What-is-the-best-way-to-optimize-this-query-tp29041515p29041515.html > Sent from the PostgreSQL - performance mailing list archive at Nabble.com. > Might help if the query were a bit more readable too: select s.*, a.actid, a.phone, d.domid, d.domname, d.domno, a.actno, a.actname, p.descr as svcdescr from vwsubsmin s inner join packages p on s.svcno=p.pkgno inner join account a on a.actno=s.actno inner join ssgdom d on a.domno=d.domno inner join (select subsno from getexpiringsubs( $1, cast($2 as integer), cast($3 as double precision), $4 ) as (subsno int, expirydt timestamp without time zone, balcpt double precision) ) as e on s.subsno=e.subsno where s.status <=15 and d.domno=$5 order by d.domname, s.expirydt, a.actname; And it would also help if the table names, column names and aliases were more self-documenting. As Rajesh said, an EXPLAIN ANALYZE output is needed, as we don't yet know where your indexes are. Regards Thom -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
