Обсуждение: query optimization

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

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
>
>

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

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.

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