Обсуждение: What is the best way to optimize this query

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.

Dear Sri,

Please post at least  the Explain Analyze output . There is a nice posting guideline
also regarding on how to post query optimization questions.

http://wiki.postgresql.org/wiki/SlowQueryQuestions

On 1 July 2010 06:19, Srikanth Kata <srikanth@inventum.net> 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