Hi,

I think im specting problems with a 7.4.8 postgres database.

Sometimes some big query takes between 5 to 15 seconds. It happens
sometimes all the day it does not depend if database is busy.

I have measured that sentence in 15 - 70 ms in normal circunstances.

Why sometimes its takes too much time?
How can I fix it?
Is a postgres version problem, database problem or query problem?

Any ideas will be apreciatted.

Ruben Rubio



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Fri, Mar 10, 2006 at 11:05:57AM +0100, Ruben Rubio Rey wrote:
> Sometimes some big query takes between 5 to 15 seconds. It happens
> sometimes all the day it does not depend if database is busy.
>
> I have measured that sentence in 15 - 70 ms in normal circunstances.

Is it the *exact* same query, including the values you're querying
for? The same query with different values can run with different
plans depending on row count estimates. It might be useful to see
the query string and the EXPLAIN ANALYZE output for a fast query
and a slow one.

How many tables are you querying? Might you be hitting geqo_threshold
(default 12)? If so then the following thread might be helpful:

http://archives.postgresql.org/pgsql...1/msg00132.php

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Michael Fuhr wrote:

>On Fri, Mar 10, 2006 at 11:05:57AM +0100, Ruben Rubio Rey wrote:
>
>
>>Sometimes some big query takes between 5 to 15 seconds. It happens
>>sometimes all the day it does not depend if database is busy.
>>
>>I have measured that sentence in 15 - 70 ms in normal circunstances.
>>
>>
>
>Is it the *exact* same query, including the values you're querying
>for? The same query with different values can run with different
>plans depending on row count estimates. It might be useful to see
>the query string and the EXPLAIN ANALYZE output for a fast query
>and a slow one.
>
>How many tables are you querying? Might you be hitting geqo_threshold
>(default 12)? If so then the following thread might be helpful:
>
>http://archives.postgresql.org/pgsql...1/msg00132.php
>
>
>

The querys that are failing are very similar. But, when I see the
warning in logs files, I take that query and try it, and takes a few
miliseconds (as spected). That query one table on FROM and 2 or 3 on
WHERE clause.

Rigth now Im using the default server configuration for geqo_threshold,
but i'll ckeck the link that u provide me.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Ruben Rubio Rey wrote:
> Hi,
>
> I think im specting problems with a 7.4.8 postgres database.
>
> Sometimes some big query takes between 5 to 15 seconds. It happens
> sometimes all the day it does not depend if database is busy.
>
> I have measured that sentence in 15 - 70 ms in normal circunstances.
>
> Why sometimes its takes too much time?
> How can I fix it?
> Is a postgres version problem, database problem or query problem?

Information, Ruben - we can't do anything without information.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Richard Huxton wrote:

> Ruben Rubio Rey wrote:
>
>> Hi,
>>
>> I think im specting problems with a 7.4.8 postgres database.
>>
>> Sometimes some big query takes between 5 to 15 seconds. It happens
>> sometimes all the day it does not depend if database is busy.
>>
>> I have measured that sentence in 15 - 70 ms in normal circunstances.
>>
>> Why sometimes its takes too much time?
>> How can I fix it?
>> Is a postgres version problem, database problem or query problem?
>
>
> Information, Ruben - we can't do anything without information.
>
What usefull information could I provide?


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

>> Information, Ruben - we can't do anything without information.
>>
> What usefull information could I provide?

Offending queries, EXPLAIN ANALYZE, tables description, condiguration
parameters, hardware, intended use...

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

unsubscribe


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend