Re: [PERFORM] timestamp indexing

2005-05-30 Thread Tobias Brox
[Michael Fuhr - Mon at 07:54:29AM -0600] The message subject is timestamp indexing but you don't mention whether you have an index on the timestamp column. Do you? Yes. Sorry for not beeing explicit on that. Could you post an example query and its EXPLAIN ANALYZE output? If the query uses

Re: [PERFORM] timestamp indexing

2005-05-30 Thread Tobias Brox
% of the rows should be selected. Forcing the usage of the index caused a tiny improvement of performance, but only after running it some few times to be sure the index got buffered :-) -- Tobias Brox, Beijing ---(end of broadcast)--- TIP 3: if posting

Re: [PERFORM] timestamp indexing

2005-05-30 Thread Tobias Brox
[Tobias Brox - Tue at 10:06:25AM +0800] [Tom Lane - Mon at 01:57:54PM -0400] Your followup hasn't shown up here yet, I'll check up on that and resend it. Hrm ... messed-up mail configuration I suppose. Here we go: Paul McGarry unintentionally sent a request for more details off the list

Re: [PERFORM] Index on a NULL-value

2005-05-30 Thread Tobias Brox
[Tobias Brox] test=# set enable_seqscan=off; [Bruno Wolff III - Mon at 10:16:53PM -0500] It isn't surprising that an index wasn't used since a sequential scan is going to be faster in your test case. If you want to test this out, you to want use realistically sized tables. Wrong

Re: [PERFORM] Index on a NULL-value

2005-05-30 Thread Tobias Brox
[Bruno Wolff III - Mon at 10:36:33PM -0500] You want something like this: CREATE INDEX b_is_null ON mock(b) WHERE b IS NULL; Oh, cool. I wasn't aware that this is possible. This would probably help us a lot of places. :-) -- Tobias Brox, Beijing ---(end of broadcast

Re: [PERFORM] Index on a NULL-value

2005-05-31 Thread Tobias Brox
of our problems (probably also regarding the index on timestamp-thread I started separately) can be solved with partial indexing on expressions. No need to hide under bedclothes anymore ;-) -- Tobias Brox, Beijing ---(end of broadcast)--- TIP 2: you can

Re: [PERFORM] Index on a NULL-value

2005-05-31 Thread Tobias Brox
[Tobias Brox - Tue at 11:02:07AM +0800] I read in the manual today: Indexes are not used for IS NULL clauses by default. The best way to use indexes in such cases is to create a partial index using an IS NULL predicate. I have summarized this thread in a postgresql doc user comment

[PERFORM] Forcing use of specific index

2005-06-01 Thread Tobias Brox
the best index, so I wonder if there is any easy way for me to check out what the planner think about a specific index :-) -- Tobias Brox, Beijing ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Need help to decide Mysql vs Postgres

2005-06-06 Thread Tobias Brox
a full table scan even if the figure can be found easily through an index. There exists a workaround: select num_attr from my_table order by num_attr desc limit 1; will find the number through the index. -- Tobias Brox, Tallinn ---(end of broadcast

Re: [PERFORM] Help with rewriting query

2005-06-08 Thread Tobias Brox
of postgresql. -- Tobias Brox, +47-91700050 Tallinn ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing

Re: [PERFORM] timestamp indexing

2005-06-09 Thread Tobias Brox
clustering night time would probably also be a solution, but I haven't cared to test it out yet. I'm a bit concerned about performance/locking issues. -- Tobias Brox, +47-91700050 Tallinn, Europe ---(end of broadcast)--- TIP 2: you can get off all lists

Re: [PERFORM] Help with rewriting query

2005-06-10 Thread Tobias Brox
. -- Tobias Brox, +47-91700050 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] Index ot being used

2005-06-10 Thread Tobias Brox
then is a sequential scan, it's no point in an index scan. -- Tobias Brox, +47-91700050 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [PERFORM] faster search

2005-06-10 Thread Tobias Brox
and typeid=9 order by productlistid,typeid,partnumber limit 15; -- Tobias Brox, +47-91700050 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [PERFORM] Help with rewriting query

2005-06-11 Thread Tobias Brox
:-) By now we have the updated balance both in the customer table and as post_balance in the transaction table. Sometimes redundancy is good. Much easier to solve inconsistency problems as well :-) -- Tobias Brox, +47-91700050 ---(end of broadcast

Re: [PERFORM] Limit clause not using index

2005-06-21 Thread Tobias Brox
with at the moment, and I don't think I should play with it ;-) -- Tobias Brox, Beijing ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your

Re: [PERFORM] Prepared statements vs. Stored Procedures

2005-06-21 Thread Tobias Brox
[Oliver Crosby - Tue at 03:46:03PM -0400] I'm hoping someone can offer some advice here. I have a large perl script that employs prepared statements to do all its queries. I'm looking at using stored procedures to improve performance times for the script. Would making a stored procedure to

Re: [PERFORM] Limit clause not using index

2005-06-21 Thread Tobias Brox
:-(. That's what I was afraid of. I was running psql at the production DB without starting a transaction (bad habit, I know) and tried to drop an index there, but I had to cancel the transaction, it took forever and in the same time blocking all the revenue-generating activity. -- Tobias Brox

Re: [PERFORM] Querying 19million records very slowly

2005-06-22 Thread Tobias Brox
(). -- Tobias Brox, +86-13521622905 Nordicbet, IT dept ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] Querying 19million records very slowly

2005-06-22 Thread Tobias Brox
really handles timezones? '+02' is quite different from 'CET', which will be obvious sometime in the late autoumn...) -- Tobias Brox, +86-13521622905 Nordicbet, IT dept ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your

Re: [PERFORM] Querying 19million records very slowly

2005-06-22 Thread Tobias Brox
scan on the first one and seqscan on the second. -- Tobias Brox, +86-13521622905 Nordicbet, IT dept ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] Querying 19million records very slowly

2005-06-22 Thread Tobias Brox
have to do that your self, or edit the file in the database data directory. -- Tobias Brox, +86-13521622905 Nordicbet, IT dept ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] Performance Tuning Article

2005-06-22 Thread Tobias Brox
known issues. It seems to me that many of the standard tips here are repeating themselves over and over again. -- Tobias Brox, +86-13521622905 Nordicbet, IT dept ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ

Re: [PERFORM] Querying 19million records very slowly

2005-06-23 Thread Tobias Brox
I'm running FreeBSD 4.11, and im editing the file in /usr/local/etc/postgresql.conf, but it doesnt help. On my system the 'live' config file resides in /var/lib/postgresql/data/postgresql.conf - maybe you have them in /usr/local/var/lib ... -- Tobias Brox, +86-13521622905 Nordicbet, IT dept

Re: [PERFORM] Planner incorrectly choosing seq scan over index scan

2005-08-01 Thread Tobias Brox
the random page cost (even to 1) but the planner still chooses to use the hash join. Have you tried increasing the statistics collection? -- Tobias Brox, +47-91700050 Nordicbet, IT dept ---(end of broadcast)--- TIP 3: Have you checked our extensive

[PERFORM] nice/low priority Query

2005-08-02 Thread Tobias Brox
. Ideally, if it was trivial to give priorities, it should be possible to keep the CPUs running at 100% for hours without causing critical problems...? -- Tobias Brox, +47-91700050 Tromso, Norway ---(end of broadcast)--- TIP 3: Have you checked our

Re: [PERFORM] nice/low priority Query

2005-08-02 Thread Tobias Brox
[Tobias Brox - Tue at 06:04:34PM +0200] (...) and at one point in the code I'm even asking the database for explain plan, grepping out the estimated cost number, and referring the user to take contact with the IT-dept if he really needs the report. But I digress. I just came to think about

[PERFORM] partial index regarded more expensive

2005-08-10 Thread Tobias Brox
to be better? ('vacuum analyze game' did not significantly impact the numbers, and I've tried running the queries some times with and without the game_by_state-index to rule out cacheing effects) -- Tobias Brox This signature has been virus scanned, and is probably safe to read. This mail may contain

Re: [PERFORM] partial index regarded more expensive

2005-08-10 Thread Tobias Brox
[PFC - Wed at 08:15:13PM +0200] why not simply create an index on (game_end, state) ? No, the planner prefers to use the partial index (I dropped the index on game(state)). -- Tobias Brox, Nordicbet IT dept This signature has been virus scanned, and is probably safe to read. This mail

[PERFORM] Limit + group + join

2005-08-25 Thread Tobias Brox
Consider this setup - which is a gross simplification of parts of our production system ;-) create table c (id integer primary key); create table b (id integer primary key, c_id integer); create index b_on_c on b(c_id) insert into c (select ... lots of IDs ...); insert into b (select

Re: [PERFORM] Limit + group + join

2005-08-25 Thread Tobias Brox
[Jeffrey W. Baker - Thu at 06:56:59PM -0700] explain select c.id from c join b on c_id=c.id group by c.id order by c.id desc limit 5; Where's b in this join clause? join b on c_id=c.id It just a funny way of writing: select c.id from c,b where c_id=c.id group by c.id order by c.id desc

Re: [PERFORM] Limit + group + join

2005-08-27 Thread Tobias Brox
[Tom Lane] I looked into this and (...) I've committed some changes that hopefully will let 8.1 be smarter about GROUP BY ... LIMIT queries. [Mark Kirkwood] Very nice :-) (...) This is 8.1devel from today. Splendid :-) Unfortunately we will not be upgrading for some monthes still, but anyway

Re: [PERFORM] Swapping

2005-08-31 Thread Tobias Brox
[Carlos Henrique Reimer - Wed at 03:25:15PM -0300] I´m trying to tune a linux box with a 12 GB database and 4 GB RAM. First of all I would like to stop the swapping, so the shared_buffers and sort_mem were decreased but even so it started swapping two hours after DBMS started up. I would

Re: [PERFORM] Swapping

2005-08-31 Thread Tobias Brox
[Tobias Brox - Wed at 09:22:17PM +0200] I'd trust linux to handle swap/cache sensibly. Eventually, become involved with kernel hacking ;-) Of course, there are also some files in /proc/sys/vm that you may want to peek into, for tuning the swapping. Particularly, at later 2.6-kernels (I'm

[PERFORM] The need for full vacuum / reindex

2005-09-27 Thread Tobias Brox
By occation, we dropped the whole production database and refreshed it from a database backup - and all our performance problems seems to have gone. I suppose this means that to keep the database efficient, one eventually does have to do reindexing and/or full vacuum from time to time? --

[PERFORM] Merge Join vs Nested Loop

2006-09-26 Thread Tobias Brox
I have some odd cases here joining two tables - the planner insists on Merge Join, but Nested Loop is really faster - and that makes sense, since I'm selecting just a small partition of the data available. All planner constants seems to be set at the default values, the only way to get a shift

Re: [PERFORM] Merge Join vs Nested Loop

2006-09-27 Thread Tobias Brox
[Tom Lane - Tue at 06:09:56PM -0400] If your tables are small enough to fit (mostly) in memory, then the planner tends to overestimate the cost of a nestloop because it fails to account for cacheing effects across multiple scans of the inner table. This is addressed in 8.2, but in earlier

Re: [PERFORM] Merge Join vs Nested Loop

2006-09-27 Thread Tobias Brox
[Scott Marlowe - Wed at 09:58:30AM -0500] Have you tried chaning the cpu_* cost options to see how they affect merge versus nested loop? As said in the original post, increasing any of them shifts the planner towards nested loops instead of merge_join. I didn't check which one of the cost

Re: [PERFORM] Merge Join vs Nested Loop

2006-09-27 Thread Tobias Brox
[Scott Marlowe - Wed at 10:19:24AM -0500] So, by decreasing them, you should move away from nested loops then, right? Has that not worked for some reason? I want to move to nested loops, they are empirically faster in many of our queries, and that makes sense since we've got quite big tables

Re: [PERFORM] Merge Join vs Nested Loop

2006-09-27 Thread Tobias Brox
[Scott Marlowe - Wed at 10:31:35AM -0500] And remember, you can always change any of those settings in session for just this one query to force the planner to make the right decision. sure ... I could identify the most problematic queries, and hack up the software application to modify the

Re: [PERFORM] autovacuum on a -mostly- r/o table

2006-09-27 Thread Tobias Brox
[Edoardo Ceccarelli - Wed at 06:49:23PM +0200] ...another thing is, how could autovacuum check for machine load, this is something I cannot imagine right now... One solution I made for our application, is to check the pg_stats_activity view. It requires some config to get the stats available

Re: [PERFORM] autovacuum on a -mostly- r/o table

2006-09-27 Thread Tobias Brox
[Edoardo Ceccarelli - Wed at 06:08:30PM +0200] We also activated the autovacuum feature to give it a try and that's were our problems started. (...) How can I configure the vacuum to run after the daily batch insert/update? I think you shouldn't use autovacuum in your case. We haven't dared

Re: [PERFORM] Merge Join vs Nested Loop

2006-09-27 Thread Tobias Brox
I found a way to survive yet some more weeks :-) One of the queries we've had most problems with today is principially something like: select A.*,sum(B.*) from A join B where A.createdx and ... order by A.created desc limit 32 group by A.* There is by average two rows in B for every row in

[PERFORM] slow queue-like empty table

2006-09-28 Thread Tobias Brox
I have a query which really should be lightning fast (limit 1 from index), but which isn't. I've checked the pg_locks table, there are no locks on the table. The database is not under heavy load at the moment, but the query seems to draw CPU power. I checked the pg_locks view, but found nothing

Re: [PERFORM] slow queue-like empty table

2006-09-28 Thread Tobias Brox
[Tobias Brox - Thu at 08:56:31AM +0200] It really seems like some transaction is still viewing the queue, since it found 38k of non-removable rows ... but how do I find the pid of the transaction viewing the queue? As said, the pg_locks didn't give me any hints ... Dropping the table

Re: [PERFORM] Poor performance on very simple query ?

2006-10-03 Thread Tobias Brox
[Arnaud Lesauvage - Tue at 01:25:10PM +0200] I have a performance problem, but I am not sure whether it really is a problem or not. QUERY PLAN

Re: [PERFORM] Poor performance on very simple query ?

2006-10-03 Thread Tobias Brox
[Tobias Brox - Tue at 02:10:04PM +0200] Did you try analyze as well? It's weird it's using seq scan, since you have a primary key it's supposed to have an index ... though 500 rows is little. I just checked up our own production database, takes 0.08 ms to fetch a row by ID from one of our

Re: [PERFORM] Poor performance on very simple query ?

2006-10-03 Thread Tobias Brox
[Arnaud Lesauvage - Tue at 02:13:59PM +0200] Tobias Brox wrote: Oh, the gid is not primary key. I guess I should also apologize for adding noise here :-) Yes, it is a primary key, but I am the noise maker here ! ;-) Oh - it is. How can you have a default value on a primary key

Re: [PERFORM] slow queue-like empty table

2006-10-04 Thread Tobias Brox
[Csaba Nagy - Thu at 10:45:35AM +0200] So you should check for idle in transaction sessions, those are bad... or any other long running transaction. Thank you (and others) for pointing this out, you certainly set us on the right track. We did have some few unclosed transactions; transactions

Re: [PERFORM] Unsubscribe

2006-10-04 Thread Tobias Brox
To be a bit constructive, could it be an idea to add unsubscribe information as one of the standard tailer tips? Then unsubscribe info wouldn't appear in every mail, but often enough for people considering to unsubscribe. To be totally non-constructive, let me add a bit to the noise below:

[PERFORM] Multi-key index not beeing used - bug?

2006-10-04 Thread Tobias Brox
Look at this: NBET= explain select * from account_transaction where users_id=123456 order by created desc limit 10; QUERY PLAN

Re: [PERFORM] Multi-key index not beeing used - bug?

2006-10-04 Thread Tobias Brox
[Tom Lane - Wed at 04:33:54PM -0400] We have indices on the users_id field and the (users_id, created)-tuple. Neither of those indexes can provide the sort order the query is asking for. Ah; that's understandable - the planner have two options, to do a index traversion without any extra

Re: [PERFORM] Simple join optimized badly?

2006-10-09 Thread Tobias Brox
[Jim C. Nasby - Mon at 04:18:27PM -0500] I can agree to that, but we'll never get any progress so long as every time hints are brought up the response is that they're evil and should never be in the database. I'll also say that a very simple hinting language (ie: allowing you to specify access

[PERFORM] long running transactions

2006-10-10 Thread Tobias Brox
While doing a verbose vacuum, I'm constantly hitting things like: DETAIL: 3606 dead row versions cannot be removed yet. I believe this is a problem, because I still do have some empty tables requireing up to 3-400 ms just to check if the table is empty (see thread slow queue-like empty table).

Re: [PERFORM] long running transactions

2006-10-10 Thread Tobias Brox
[Tom Lane - Tue at 12:23:40PM -0400] Look in pg_locks to see the lowest-numbered transaction ID --- each transaction will be holding exclusive lock on its own XID. You can correlate that back to pg_stat_activity via the PID. Thanks a lot for the quick reply - I've already identified one

Re: [PERFORM] long running transactions

2006-10-10 Thread Tobias Brox
[Tobias Brox - Tue at 06:39:13PM +0200] Thanks a lot for the quick reply - I've already identified one long-running transaction. belonging to autovacuum ... how come? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore

Re: [PERFORM] long running transactions

2006-10-10 Thread Tobias Brox
[Tom Lane - Tue at 12:42:52PM -0400] belonging to autovacuum ... how come? Blocked on someone else's lock, maybe? hardly, the autovacuum is the only one having such a low transaction id, and also the only one hanging around when waiting a bit and rechecking the pg_locks table.

Re: [PERFORM] long running transactions

2006-10-10 Thread Tobias Brox
[Tom Lane - Tue at 01:18:27PM -0400] Hmph. Is the autovac process actually doing anything (strace would be revealing)? It's definitively doing something; mostly reading, but also some few writes, semops and opens. If not, can you attach to the autovac process with gdb and get a stack

Re: [PERFORM] long running transactions

2006-10-10 Thread Tobias Brox
[Tom Lane - Tue at 02:04:55PM -0400] It seems stuck, has had the same transid for a long while, and the number of undeletable dead rows in our tables are increasing. Perhaps you have overly aggressive vacuum cost delay settings? Perhaps, though I wouldn't expect it to sleep in the middle

Re: [PERFORM] long running transactions

2006-10-10 Thread Tobias Brox
[Tom Lane - Tue at 02:26:53PM -0400] autovacuum_vacuum_cost_delay = 500 autovacuum_vacuum_cost_limit = 200 Well, that's going to cause it to sleep half a second after every dozen or so page I/Os. I think you'd be well advised to reduce the delay. Modified it to 20/250, and it

Re: [ADMIN] [PERFORM] autovacuum on a -mostly- r/o table

2006-10-15 Thread Tobias Brox
[Matthew T. O'Connor - Wed at 02:33:10PM -0400] In addition autovacuum respects the work of manual or cron based vacuums, so if you issue a vacuum right after a daily batch insert / update, autovacuum won't repeat the work of that manual vacuum. I was experimenting a bit with autovacuum now.

Re: [ADMIN] [PERFORM] autovacuum on a -mostly- r/o table

2006-10-15 Thread Tobias Brox
[Matthew T. O'Connor - Sun at 10:42:34AM -0400] Yeah, I think if the delay settings are too high it can cause problems, that's part of the reason we have yet to turn these on be default since we won't have enough data to suggest good values. Can you tell us what settings you finally

[PERFORM] Swappiness setting on a linux pg server

2006-10-19 Thread Tobias Brox
I just came to think about /proc/sys/swappiness ... When this one is set to a high number (say, 100 - which is maximum), the kernel will aggressively swap out all memory that is not beeing accessed, to allow more memory for caches. For a postgres server, OS caches are good, because postgres

Re: [PERFORM] Swappiness setting on a linux pg server

2006-10-19 Thread Tobias Brox
[Jim C. Nasby - Thu at 10:28:31AM -0500] I think it'd be much better to experiment with using much larger shared_buffers settings. The conventional wisdom there is from 7.x days when you really didn't want a large buffer, but that doesn't really apply with the new buffer management we got in

Re: [PERFORM] Swappiness setting on a linux pg server

2006-10-19 Thread Tobias Brox
[Jim C. Nasby - Thu at 10:28:31AM -0500] I think it'd be much better to experiment with using much larger shared_buffers settings. The conventional wisdom there is from 7.x days when you really didn't want a large buffer, but that doesn't really apply with the new buffer management we got in

Re: [PERFORM] Swappiness setting on a linux pg server

2006-10-19 Thread Tobias Brox
[Jim C. Nasby - Thu at 11:31:26AM -0500] Yeah, test setups are a good thing to have... We would need to replicate the production traffic as well to do reliable tests. Well, we'll get to that one day ... The issue with pg_xlog is you don't need bandwidth... you need super-low latency. The

Re: [PERFORM] Swappiness setting on a linux pg server

2006-10-19 Thread Tobias Brox
[Jim C. Nasby - Thu at 11:45:32AM -0500] The issue with pg_xlog is you don't need bandwidth... you need super-low latency. The best way to accomplish that is to get a battery-backed RAID controller that you can enable write caching on. Sounds a bit risky to me :-) Well, you do

Re: [PERFORM] Swappiness setting on a linux pg server

2006-10-19 Thread Tobias Brox
[Jim C. Nasby - Thu at 12:00:39PM -0500] What's reasonable for work_mem depends on your workload. If you've got some reporting queries that you know aren't run very concurrently they might benefit from large values of work_mem. For stats.distributed.net, I set work_mem to something like 2MB in

Re: [PERFORM] Swappiness setting on a linux pg server

2006-10-19 Thread Tobias Brox
[Jim C. Nasby - Thu at 12:00:39PM -0500] Well, if you're buying unreliable hardware, there's not much you can do... you're setting yourself up for problems. I'm luckily not responsible for the hardware, but my general experience tells that you never know anything about hardware reliability

Re: [PERFORM] Swappiness setting on a linux pg server

2006-10-19 Thread Tobias Brox
[Ron - Thu at 03:10:35PM -0400] Jim is correct that traditional 7.x folklore regarding shared buffer size is nowhere near as valid for 8.x. Jim tends to know what he is talking about when speaking about pg operational issues. I would not doubt it, but it's always better to hear it from more

Re: [PERFORM] Swappiness setting on a linux pg server

2006-10-20 Thread Tobias Brox
[Jim C. Nasby - Thu at 11:31:26AM -0500] The issue with pg_xlog is you don't need bandwidth... you need super-low latency. The best way to accomplish that is to get a battery-backed RAID controller that you can enable write caching on. In fact, if the controller is good enough, you can

Re: [PERFORM] Setting nice values

2006-11-02 Thread Tobias Brox
[Madison Kelly - Thu at 10:25:07AM -0500] Will the priority of the script pass down to the pgsql queries it calls? I figured (likely incorrectly) that because the queries were executed by the psql server the queries ran with the server's priority. I think you are right, and in any case, I

Re: [PERFORM] Setting nice values

2006-11-06 Thread Tobias Brox
[Madison Kelly - Mon at 08:10:12AM -0500] to run, which puts it into your drawback section. The server in question is also almost under load of some sort, too. A great tip and one I am sure to make use of later, thanks! I must have been sleepy, listing up cons vs drawbacks ;-) Anyway, the

Re: [PERFORM] Setting nice values

2006-11-06 Thread Tobias Brox
[Madison Kelly - Mon at 08:48:19AM -0500] Ah, sorry, long single queries is what you meant. No - long running single transactions :-) If it's only read-only queries, one will probably benefit by having one transaction for every query. ---(end of

Re: [PERFORM] Defining performance.

2006-11-30 Thread Tobias Brox
[Paul Lathrop - Thu at 02:59:27PM -0800] growing disk space usage. The DBA had come to the conclusion that the VACUUM command did/does not work on these systems, because even after a VACUUM FULL, the size of the database was continually increasing. So, as things stand with the PG7.2 machines,

Re: [PERFORM] Defining performance.

2006-11-30 Thread Tobias Brox
[Jeff Davis - Thu at 04:57:54PM -0800] We're having the same issues, so we do the dumping and restoring every now and then to be sure everything is properly cleaned up. With 8.1. What's causing that? Is it index bloat? I would think a REINDEX would avoid having to dump/restore,

Re: [PERFORM] Defining performance.

2006-11-30 Thread Tobias Brox
[EMAIL PROTECTED] - Thu at 06:37:12PM -0600] As my dataset has gotten larger I have had to throw more metal at the problem, but I have also had to rethink my table and query design. Just because your data set grows linearly does NOT mean that the performance of your query is guaranteed to

Re: [PERFORM] Defining performance.

2006-11-30 Thread Tobias Brox
[Chris - Fri at 02:32:05PM +1100] Not really. A bad query is a bad query (eg missing a join element). It won't show up for 3000 rows, but will very quickly if you increase that by a reasonable amount. Even as simple as a missing index on a join column won't show up for a small dataset but

[PERFORM] max/min and index usage

2006-12-05 Thread Tobias Brox
We're using 8.1 - I thought such a construct was safe in pg 8.1: select max(indexed_value) from huge_table; while earlier we had to use: select indexed_value from huge_table order by indexed_value desc limit 1; seems like I was wrong: mydb= explain analyze select indexed_value1 from

Re: [PERFORM] max/min and index usage

2006-12-05 Thread Tobias Brox
[Tobias Brox - Wed at 04:01:56AM +0100] We're using 8.1 - I thought such a construct was safe in pg 8.1: select max(indexed_value) from huge_table; while earlier we had to use: select indexed_value from huge_table order by indexed_value desc limit 1; seems like I was wrong

Re: [PERFORM] max/min and index usage

2006-12-05 Thread Tobias Brox
[Tom Lane - Tue at 10:29:53PM -0500] These are not actually exactly the same thing. In particular, I suppose your table contains a lot of nulls? Yes; I'm sorry I was a bit quick with the first posting. ---(end of broadcast)--- TIP 1: if

Re: [PERFORM] Missing the point of autovacuum

2007-01-06 Thread Tobias Brox
[Daryl Herzmann - Sat at 12:59:03PM -0600] As the months have gone by, I notice many of my tables having *lots* of unused item pointers. For example, Probably not the issue here, but we had some similar issue where we had many long-running transactions - i.e. some careless colleague entering

[PERFORM] Planner statistics, correlations

2007-01-12 Thread Tobias Brox
We have a table with a timestamp attribute (event_time) and a state flag which usually changes value around the event_time (it goes to 4). Now we have more than two years of events in the database, and around 5k of future events. It is important to frequently pick out overdue events, say:

Re: [PERFORM] Planner statistics, correlations

2007-01-12 Thread Tobias Brox
[Peter Childs - Fri at 08:56:54AM +] Can you say what state might be rather than what it is not. I'm guess that state is an int but there is only a limited list of possible states, if you can say what it might be rather than what it is the index is more liklly to be used. explain select

Re: [PERFORM] Planner statistics, correlations

2007-01-12 Thread Tobias Brox
[Richard Huxton - Fri at 09:17:48AM +] Try a partial index: CREATE INDEX my_new_index ON events (event_time) WHERE state in (1,2,3); I have that, the index is used and the query is lightning fast - the only problem is that the planner is using the wrong estimates. This becomes a real

Re: [PERFORM] Planner statistics, correlations

2007-01-12 Thread Tobias Brox
[Heikki Linnakangas - Fri at 10:41:34AM +] I thought about partitioning the table by state, putting rows with state=4 into one partition, and all others to another partition. That sounds like a good idea - but wouldn't that be costly when changing state? ---(end of

[PERFORM] extract(field from timestamp) vs date dimension

2007-01-23 Thread Tobias Brox
Does anyone have experience with using postgres for data warehousing? Right, I saw one post suggestion to use mysql for a mostly read-only database ... but anyway, I think it's not a question to change the database platform for this project, at least not today ;-) Ralph Kimball seems to be some

Re: [PERFORM] extract(field from timestamp) vs date dimension

2007-01-23 Thread Tobias Brox
[Chad Wagner - Tue at 08:24:34AM -0500] I guess go with your gut, but at some point the expressions are going to be too complicated to maintain, and inefficient. The layout of my system is quite flexible, so it should eventually be fairly trivial to throw in a date dimension at a later stage.

Re: OT: Munin (was Re: [PERFORM] Determining server load from client)

2007-03-21 Thread Tobias Brox
I have my postgres munin monitoring script at http://oppetid.no/~tobixen/pg_activity.munin.txt (had to suffix it with .txt to make the local apache happy). I would like to see what others have done as well. ---(end of broadcast)--- TIP 1: if

Re: OT: Munin (was Re: [PERFORM] Determining server load from client)

2007-03-21 Thread Tobias Brox
[Erik Jones - Wed at 09:31:48AM -0500] I use cacti (http://cacti.net) which does the same thing that munin does but in php instead. Here's what I use to db stats to it (again, php): I haven't tried cacti, but our sysadm has done a little bit of research and concluded cacti is better.

[PERFORM] Long running transactions again ...

2007-04-10 Thread Tobias Brox
We had problems again, caused by long running transactions. I'm monitoring the pg_stat_activity view, checking the query_start of all requests that are not idle - but this one slipped under the radar as the application was running frequent queries towards the database. That's not what concerns

Re: [PERFORM] pg_stat_* collection

2007-05-03 Thread Tobias Brox
[Alexander Staubo - Thu at 04:52:55PM +0200] I have been considering tarring them up as a proper release at some point. Anyone interested? Yes. Eventually I have my own collection as well: db_activity - counts the number of (all, slow, very slow, stuck idle in transaction) queries in

Re: [PERFORM] pg_stat_* collection

2007-05-03 Thread Tobias Brox
[Greg Smith - Fri at 12:53:55AM -0400] Munin is a very interesting solution to this class of problem. They've managed to streamline the whole data collection process by layering clever Perl hacks three deep. It's like the anti-SNMP--just build the simplest possible interface that will

Re: [PERFORM] Performance Testing Utility

2007-06-13 Thread Tobias Brox
[Christo Du Preez - Wed at 12:25:20PM +0200] Is there some kind of performance testing utility available for postgresql Something I can run after installing postgresql to help me identify if my installation is optimal. I've been battling for days now trying to sort out performance issues

Re: [PERFORM] Long running transaction in pg_activity_log

2007-08-22 Thread Tobias Brox
[Sachchida Ojha - Wed at 04:40:09PM -0400] I see some long running transaction in my pg_activity_log table. My app becomes almost unusable. My question is How can I query the database to see what sql these transactions are running. IDLE in transaction means that no sql query is running at

Re: [PERFORM] SAN vs Internal Disks

2007-09-07 Thread Tobias Brox
We're also considering to install postgres on SAN - that is, my boss is convinced this is the right way to go. Advantages: 1. Higher I/O (at least the salesman claims so) 2. Easier to upgrade the disk capacity 3. Easy to set up warm standby functionality. (Then again, if the postgres server

Re: [PERFORM] SAN vs Internal Disks

2007-09-11 Thread Tobias Brox
[Decibel! - Tue at 06:07:44PM -0500] It's still in the same chassis, though, which means if you lose memory or mobo you're still screwed. In a SAN setup for redundancy, there's very little in the way of a single point of failure; generally only the backplane, and because there's very little

Re: [PERFORM] autovacuum: recommended?

2007-11-16 Thread Tobias Brox
[Gábor Farkas - Fri at 10:40:43AM +0100] my question is: is it recommended to use it? or in other words, should i only use autovacuum? or it's better to use manual-vacuuming? which one is the way of the future :) ? or should i use both auto-vacuum and manual-vacuum? Nightly vacuums are

Re: [PERFORM] autovacuum: recommended?

2007-11-18 Thread Tobias Brox
[EMAIL PROTECTED] The table was quite huge (say 20k of products along with detailed descriptions etc.) and was completely updated and about 12x each day, i.e. it qrew to about 12x the original size (and 11/12 of the rows were dead). This caused a serious slowdown of the application each day,

Re: [PERFORM] Anyone using a SAN?

2008-02-13 Thread Tobias Brox
[Peter Koczan - Wed at 10:56:54AM -0600] We're considering setting up a SAN where I work. Is there anyone using a SAN, for postgres or other purposes? If so I have a few questions for you. Some time ago, my boss was planning to order more hardware - including a SAN - and coincidentally, SANs

  1   2   >