Re: [GENERAL] Statistics collection question

2007-09-16 Thread Phoenix Kiula
Well first question: how can I check if autovacuum is working? On 04/09/2007, Tom Lane [EMAIL PROTECTED] wrote: Phoenix Kiula [EMAIL PROTECTED] writes: Basically, what I am missing is some info on actually tweaking the postgresql.conf to suit my system. No, that's *not* what you're

Re: [GENERAL] Statistics collection question

2007-09-10 Thread [EMAIL PROTECTED]
On Sep 4, 10:54 pm, [EMAIL PROTECTED] (Tom Lane) wrote: Phoenix Kiula [EMAIL PROTECTED] writes: Would appreciate any help. Why do indexed queries take so much time? It's a simple DB with 10 relations including tables and indexes. Simple inserts and updates, about 5000 a day, but non-trivial

Re: [GENERAL] Statistics collection question

2007-09-10 Thread Martijn van Oosterhout
On Mon, Sep 10, 2007 at 07:05:54PM -, [EMAIL PROTECTED] wrote: When I do a select * from pg_locks, some of them show up as Exclusive Lock. This I suppose means that the whole table is locked, right? How can I find from the transaction id which precise SQL statement is taking this time? I

Re: [GENERAL] Statistics collection question

2007-09-04 Thread Phoenix Kiula
On 04/09/07, Tom Lane [EMAIL PROTECTED] wrote: Phoenix Kiula [EMAIL PROTECTED] writes: On 04/09/07, Richard Broersma Jr [EMAIL PROTECTED] wrote: Thats odd, I wonder why the EXPLAIN ANALYZE time id some much less that the logged select statement times? Because the statement has been

Re: [GENERAL] Statistics collection question

2007-09-04 Thread Gregory Williamson
9/4/2007 1:07 AM To: Tom Lane Cc: Richard Broersma Jr; Alban Hertroys; Postgres General Subject: Re: [GENERAL] Statistics collection question On 04/09/07, Tom Lane [EMAIL PROTECTED] wrote: Phoenix Kiula [EMAIL PROTECTED] writes: On 04/09/07, Richard Broersma Jr [EMAIL PROTECTED] wrote: Thats

Re: [GENERAL] Statistics collection question

2007-09-04 Thread Martijn van Oosterhout
On Tue, Sep 04, 2007 at 03:07:41PM +0800, Phoenix Kiula wrote: How can I check what is causing the lack? When I restart pgsql it goes away. The log is empty for a day or too (I'm only logging errors or slow queries) and the queries are super fast, but after a day it starts filling up with

Re: [GENERAL] Statistics collection question

2007-09-04 Thread Alban Hertroys
Phoenix Kiula wrote: On 04/09/07, Tom Lane [EMAIL PROTECTED] wrote: I'm wondering about some transaction taking exclusive lock on the table and sitting on it for a minute or so, and also about network problems delaying transmission of data to the client. How can I check what is causing the

Re: [GENERAL] Statistics collection question

2007-09-04 Thread Tom Lane
Phoenix Kiula [EMAIL PROTECTED] writes: Basically, what I am missing is some info on actually tweaking the postgresql.conf to suit my system. No, that's *not* what you're missing. I'm not sure what the problem is in your system, but I'm pretty sure that everything you have frantically been

Re: [GENERAL] Statistics collection question

2007-09-04 Thread Tom Lane
Phoenix Kiula [EMAIL PROTECTED] writes: Would appreciate any help. Why do indexed queries take so much time? It's a simple DB with 10 relations including tables and indexes. Simple inserts and updates, about 5000 a day, but non-trivial concurrent selects (about 45 million a day). Works fine

[GENERAL] Statistics collection question

2007-09-03 Thread Phoenix Kiula
A couple of questions about the most_common_vals stuff in pg_stats for a high traffic table: 1. Can I tell the stats collector to collect only values of a column where a certain regex is matched? It is currently collecting the 500 values where most of them are values that I don't want, so it's

Re: [GENERAL] Statistics collection question

2007-09-03 Thread Tom Lane
Phoenix Kiula [EMAIL PROTECTED] writes: A couple of questions about the most_common_vals stuff in pg_stats for a high traffic table: 1. Can I tell the stats collector to collect only values of a column where a certain regex is matched? Not directly, but you could set up a partial index

Re: [GENERAL] Statistics collection question

2007-09-03 Thread Phoenix Kiula
On 03/09/07, Tom Lane [EMAIL PROTECTED] wrote: Phoenix Kiula [EMAIL PROTECTED] writes: most_common_vals will (and should) be empty if there aren't actually any common values, but aren't you getting a histogram? Exactly what performance do you think will be improved? Lots of posts here in

Re: [GENERAL] Statistics collection question

2007-09-03 Thread Alvaro Herrera
Phoenix Kiula escribió: On 03/09/07, Tom Lane [EMAIL PROTECTED] wrote: Phoenix Kiula [EMAIL PROTECTED] writes: most_common_vals will (and should) be empty if there aren't actually any common values, but aren't you getting a histogram? Exactly what performance do you think will be

Re: [GENERAL] Statistics collection question

2007-09-03 Thread Alban Hertroys
Phoenix Kiula wrote: Lots of posts here in reponse to performance question have the recommendation increase the stats on that column. From whatever succint reading is made available on the postgres site, I gather that this aids the planner in getting some info about some of the data. Am I

Re: [GENERAL] Statistics collection question

2007-09-03 Thread Phoenix Kiula
On 03/09/07, Alban Hertroys [EMAIL PROTECTED] wrote: Phoenix Kiula wrote: As I understand it it's a sample of how the data is distributed. Probably it's based on statistical mathematics that specifies a minimum size for a representive sample of a given data set. It boils down to: If you want

Re: [GENERAL] Statistics collection question

2007-09-03 Thread Richard Broersma Jr
--- Phoenix Kiula [EMAIL PROTECTED] wrote: LOG: duration: 93473.282 ms statement: select t_info, dstats, id from trades where t_alias = '17huv' and status = 'Y' --- Index Scan using trades_unique_t_alias on trades (cost=0.00..3.41 Time: 2.990 ms Thats odd, I

Re: [GENERAL] Statistics collection question

2007-09-03 Thread Phoenix Kiula
On 04/09/07, Richard Broersma Jr [EMAIL PROTECTED] wrote: --- Phoenix Kiula [EMAIL PROTECTED] wrote: LOG: duration: 93473.282 ms statement: select t_info, dstats, id from trades where t_alias = '17huv' and status = 'Y' --- Index Scan using trades_unique_t_alias