pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWhat is the best way to optimize this query
3+ messages / 3 participants
[nested] [flat]
* What is the best way to optimize this query
@ 2010-07-01 05:19 Srikanth Kata <srikanth@inventum.net>
0 siblings, 2 replies; 3+ messages in thread
From: Srikanth Kata @ 2010-07-01 05:19 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: What is the best way to optimize this query
@ 2010-07-01 12:40 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
parent: Srikanth Kata <srikanth@inventum.net>
1 sibling, 0 replies; 3+ messages in thread
From: Rajesh Kumar Mallah @ 2010-07-01 12:40 UTC (permalink / raw)
To: Srikanth Kata <srikanth@inventum.net>; +Cc: pgsql-performance
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 Thu, Jul 1, 2010 at 10:49 AM, 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.
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: What is the best way to optimize this query
@ 2010-07-01 12:51 Thom Brown <thombrown@gmail.com>
parent: Srikanth Kata <srikanth@inventum.net>
1 sibling, 0 replies; 3+ messages in thread
From: Thom Brown @ 2010-07-01 12:51 UTC (permalink / raw)
To: Srikanth Kata <srikanth@inventum.net>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-07-01 12:51 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-07-01 05:19 What is the best way to optimize this query Srikanth Kata <srikanth@inventum.net>
2010-07-01 12:40 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2010-07-01 12:51 ` Thom Brown <thombrown@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox