Re: [GENERAL] very slow queries and ineffective vacuum

2015-07-03 7:18 GMT+02:00 Sameer Kumar : > > > On Thu, Jul 2, 2015 at 9:57 PM Lukasz Wrobel < > lukasz.wro...@motorolasolutions.com> wrote: > >> Hello again. >> >> Thank you for all your responses. I will try to clarify more and attempt >> to answer the questions you raised. >> >> I'm attaching th

Re: [GENERAL] very slow queries and ineffective vacuum

On Thu, Jul 2, 2015 at 9:57 PM Lukasz Wrobel < lukasz.wro...@motorolasolutions.com> wrote: > Hello again. > > Thank you for all your responses. I will try to clarify more and attempt > to answer the questions you raised. > > I'm attaching the postgresql.conf this time. I cannot supply you guys wit

Re: [GENERAL] very slow queries and ineffective vacuum

On Thu, 2 Jul 2015 12:58:18 +0200 Lukasz Wrobel wrote: > Hello again. > > Thank you for all your responses. I will try to clarify more and attempt to > answer the questions you raised. > > I'm attaching the postgresql.conf this time. I cannot supply you guys with > a proper database schema, so

Re: [GENERAL] very slow queries and ineffective vacuum

Well, right off the bat, it looks like you do not have indexes on table84.col7 table57.col7 table19.col7 At least a quick review of the query plan shows they are not being used if they do exist. So perhaps that is one of the chief causes for slow performance. On Thu, Jul 2, 2015 at 6:58 AM, Luka

Re: [GENERAL] very slow queries and ineffective vacuum

On Wed, Jul 1, 2015 at 4:51 AM Pavel Stehule wrote: > Hi > > What is an output of VACUUM VERBOSE statement? > > VACUUM can be blocked by some forgotten transaction. Check your > pg_stat_activity table for some old process in "idle in transaction" state. > Then connection should not be reused, and

Re: [GENERAL] very slow queries and ineffective vacuum

On Wed, Jul 1, 2015 at 3:37 AM Lukasz Wrobel < lukasz.wro...@motorolasolutions.com> wrote: > Hello. > > I have multiple problems with my database, the biggest of which is how to > find out what is actually wrong. > > First of all I have a 9.3 postgres database that is running for about a > month.

Re: [GENERAL] very slow queries and ineffective vacuum

How about your start by giving us a little useful information? Show us your_longest_query and the output from EXPLAIN your_longest_query; Although you say you have indexes, they may not be the correct indexes that you really need. Also, how many physical disks do you have? Do you have multiple tabl

Re: [GENERAL] very slow queries and ineffective vacuum

Lukasz Wrobel wrote: > Hello. > > I have multiple problems with my database, the biggest of which is how to > find out what is actually wrong. > > First of all I have a 9.3 postgres database that is running for about a > month. Right now the queries on that database are running very slowly > (sel

Re: [GENERAL] very slow queries and ineffective vacuum

Jerry, On Tue, Jun 30, 2015 at 5:31 PM, Jerry Sievers wrote: > > foodb/postgres > =# \d pg_stat_activity|pg_prepared_xacts > View "pg_catalog.pg_prepared_xacts" >Column| Type | Modifiers > -+--+--- > transaction |

Re: [GENERAL] very slow queries and ineffective vacuum

William Dunn writes: > Sorry I meant to say, "To track transactions that have been left idle but not > committed or rolled back you would..." > Typo foodb/postgres =# \d pg_stat_activity|pg_prepared_xacts View "pg_catalog.pg_prepared_xacts" Column| Type | Mod

Re: [GENERAL] very slow queries and ineffective vacuum

Hi What is an output of VACUUM VERBOSE statement? VACUUM can be blocked by some forgotten transaction. Check your pg_stat_activity table for some old process in "idle in transaction" state. Then connection should not be reused, and you can see a error messages about missing connections. I found t

Re: [GENERAL] very slow queries and ineffective vacuum

Sorry I meant to say, "To track transactions that *have been* left idle but not committed or rolled back you would..." Typo *Will J. Dunn* *willjdunn.com * On Tue, Jun 30, 2015 at 4:33 PM, William Dunn wrote: > On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel < > lukasz.wro.

Re: [GENERAL] very slow queries and ineffective vacuum

On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel < lukasz.wro...@motorolasolutions.com> wrote: > > > Perhaps I'm missing some indexes on the tables (creating them on the > columns on which the where clause was used in the long queries seemed to > halve their times). Also how can I monitor my transact

Re: [GENERAL] very slow queries and ineffective vacuum

Hello Lukasz, On Tue, Jun 30, 2015 at 8:57 AM, Lukasz Wrobel < lukasz.wro...@motorolasolutions.com> wrote: > > There doesn't seem to be any issues with disk space, memory or CPU, as > neither of those is even 50% used (as per df and top). > Are you using the default PostgreSQL configuration sett

[GENERAL] very slow queries and ineffective vacuum

Hello. I have multiple problems with my database, the biggest of which is how to find out what is actually wrong. First of all I have a 9.3 postgres database that is running for about a month. Right now the queries on that database are running very slowly (select with a simple "where" on a non-in