pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedquery optimization
6+ messages / 4 participants
[nested] [flat]
* query optimization
@ 2008-07-10 09:01 Suresh Borse <s.borse@direction.biz>
2008-07-10 09:25 ` Re: query optimization Julius Tuskenis <julius@nsoft.lt>
0 siblings, 1 reply; 6+ messages in thread
From: Suresh Borse @ 2008-07-10 09:01 UTC (permalink / raw)
To: pgsql-admin
Dear All,
I am facing very strange problem in postgres while executing the view.
When I execute the view some times it takes few milliseconds, sometimes
it take several seconds and some times doesn't execute.
I tried to troubleshoot using the explain plan method. But explain plan
also changes for each execution.
Can anybody help me for troubleshooting and for resolving the
performance issue.
Thanks & Regards,
Suresh Borse
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: query optimization
2008-07-10 09:01 query optimization Suresh Borse <s.borse@direction.biz>
@ 2008-07-10 09:25 ` Julius Tuskenis <julius@nsoft.lt>
2008-07-10 09:45 ` Re: query optimization Suresh Borse <s.borse@direction.biz>
0 siblings, 1 reply; 6+ messages in thread
From: Julius Tuskenis @ 2008-07-10 09:25 UTC (permalink / raw)
To: s.borse@direction.biz; +Cc: pgsql-admin
Hello, Suresh
Maybe its taking long for the first time, and then its faster? Did you
noticed any circumstances under witch your query takes longer to execute?
Suresh Borse rašė:
> Dear All,
>
> I am facing very strange problem in postgres while executing the view.
>
> When I execute the view some times it takes few milliseconds,
> sometimes it take several seconds and some times doesn't execute.
>
> I tried to troubleshoot using the explain plan method. But explain
> plan also changes for each execution.
>
> Can anybody help me for troubleshooting and for resolving the
> performance issue.
>
>
> Thanks & Regards,
> Suresh Borse
>
>
--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: query optimization
2008-07-10 09:01 query optimization Suresh Borse <s.borse@direction.biz>
2008-07-10 09:25 ` Re: query optimization Julius Tuskenis <julius@nsoft.lt>
@ 2008-07-10 09:45 ` Suresh Borse <s.borse@direction.biz>
2008-07-10 10:39 ` Re: query optimization Julius Tuskenis <julius@nsoft.lt>
2008-07-10 15:47 ` Re: query optimization Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Suresh Borse @ 2008-07-10 09:45 UTC (permalink / raw)
To: Julius Tuskenis <julius@nsoft.lt>; +Cc: pgsql-admin
Good Afternoon !!!
The view executes very faster for the first time.
When I execute the view again and again suddenly it gets slow or it gets
hang.
I have also traced the query plan but it changes for each execution.Can
we fix the query plan.
I have also tested the view on my test machine but the result was same.
The view contains multi-table joins.
Warm Regards,
Suresh Borse
On Thu, 2008-07-10 at 12:25 +0300, Julius Tuskenis wrote:
> Hello, Suresh
>
> Maybe its taking long for the first time, and then its faster? Did you
> noticed any circumstances under witch your query takes longer to execute?
>
>
> Suresh Borse rašė:
> > Dear All,
> >
> > I am facing very strange problem in postgres while executing the view.
> >
> > When I execute the view some times it takes few milliseconds,
> > sometimes it take several seconds and some times doesn't execute.
> >
> > I tried to troubleshoot using the explain plan method. But explain
> > plan also changes for each execution.
> >
> > Can anybody help me for troubleshooting and for resolving the
> > performance issue.
> >
> >
> > Thanks & Regards,
> > Suresh Borse
> >
> >
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: query optimization
2008-07-10 09:01 query optimization Suresh Borse <s.borse@direction.biz>
2008-07-10 09:25 ` Re: query optimization Julius Tuskenis <julius@nsoft.lt>
2008-07-10 09:45 ` Re: query optimization Suresh Borse <s.borse@direction.biz>
@ 2008-07-10 10:39 ` Julius Tuskenis <julius@nsoft.lt>
1 sibling, 0 replies; 6+ messages in thread
From: Julius Tuskenis @ 2008-07-10 10:39 UTC (permalink / raw)
To: s.borse@direction.biz; +Cc: pgsql-admin
Please send the "explain analyze YOUR_QUERY" output after first run and
later. Also please mention platform and pg version you are using.
Suresh Borse rašė:
>
> Good Afternoon !!!
>
> The view executes very faster for the first time.
>
> When I execute the view again and again suddenly it gets slow or it
> gets hang.
>
> I have also traced the query plan but it changes for each
> execution.Can we fix the query plan.
>
> I have also tested the view on my test machine but the result was same.
>
> The view contains multi-table joins.
>
> Warm Regards,
> Suresh Borse
>
>
>
> On Thu, 2008-07-10 at 12:25 +0300, Julius Tuskenis wrote:
>> Hello, Suresh
>>
>> Maybe its taking long for the first time, and then its faster? Did you
>> noticed any circumstances under witch your query takes longer to execute?
>>
>>
>> Suresh Borse rašė:
>> > Dear All,
>> >
>> > I am facing very strange problem in postgres while executing the view.
>> >
>> > When I execute the view some times it takes few milliseconds,
>> > sometimes it take several seconds and some times doesn't execute.
>> >
>> > I tried to troubleshoot using the explain plan method. But explain
>> > plan also changes for each execution.
>> >
>> > Can anybody help me for troubleshooting and for resolving the
>> > performance issue.
>> >
>> >
>> > Thanks & Regards,
>> > Suresh Borse
>> >
>> >
>>
>>
>>
--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: query optimization
2008-07-10 09:01 query optimization Suresh Borse <s.borse@direction.biz>
2008-07-10 09:25 ` Re: query optimization Julius Tuskenis <julius@nsoft.lt>
2008-07-10 09:45 ` Re: query optimization Suresh Borse <s.borse@direction.biz>
@ 2008-07-10 15:47 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-07-10 16:21 ` Re: query optimization Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Scott Marlowe @ 2008-07-10 15:47 UTC (permalink / raw)
To: s.borse@direction.biz; +Cc: Julius Tuskenis <julius@nsoft.lt>; pgsql-admin
On Thu, Jul 10, 2008 at 3:45 AM, Suresh Borse <s.borse@direction.biz> wrote:
>
> Good Afternoon !!!
>
> The view executes very faster for the first time.
>
> When I execute the view again and again suddenly it gets slow or it gets
> hang.
>
> I have also traced the query plan but it changes for each execution.Can we
> fix the query plan.
>
> I have also tested the view on my test machine but the result was same.
>
> The view contains multi-table joins.
Do you have different where clauses for each time you execute it?
That can certainly affect performance each time, if you're where
clause is gonna grab 99% of the view, you'll get a different plan than
if you have a where clause that selects 0.01% of the table.
So yeah, we need explain analyze of each type of query that's fast
slow, and if there are ANY differences between them we need to know.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: query optimization
2008-07-10 09:01 query optimization Suresh Borse <s.borse@direction.biz>
2008-07-10 09:25 ` Re: query optimization Julius Tuskenis <julius@nsoft.lt>
2008-07-10 09:45 ` Re: query optimization Suresh Borse <s.borse@direction.biz>
2008-07-10 15:47 ` Re: query optimization Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-07-10 16:21 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2008-07-10 16:21 UTC (permalink / raw)
To: s.borse@direction.biz, "Julius Tuskenis" <julius@nsoft.lt>; pgsql-admin
On Thu, Jul 10, 2008 at 3:45 AM, Suresh Borse <s.borse@direction.biz> wrote:
>> The view executes very faster for the first time.
>> When I execute the view again and again suddenly it gets slow or it gets
>> hang.
>> The view contains multi-table joins.
Uh, how many joins?
If you're exceeding geqo_threshold then you would get plans with a
random component to them. You might want to increase the threshold,
although planning time would probably go up.
Also, what PG version is this?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2008-07-10 16:21 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-07-10 09:01 query optimization Suresh Borse <s.borse@direction.biz>
2008-07-10 09:25 ` Julius Tuskenis <julius@nsoft.lt>
2008-07-10 09:45 ` Suresh Borse <s.borse@direction.biz>
2008-07-10 10:39 ` Julius Tuskenis <julius@nsoft.lt>
2008-07-10 15:47 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-07-10 16:21 ` Tom Lane <tgl@sss.pgh.pa.us>
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