Re: [PERFORM] Query tuning

2006-08-23 Thread Subbiah, Stalin
Changing limit or offset to a small number doesn't have any change in plans. Likewise enable_seqscan to false. They still take 8-10 mins to runs. -Original Message- From: Dave Dutcher [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 23, 2006 4:20 PM To: Subbiah, Stalin Cc: pgsql-perform

Re: [PERFORM] Forcing index usage without 'enable_hashjoin = FALSE'

2006-08-23 Thread Dan Langille
On 23 Aug 2006 at 22:30, Tom Lane wrote: > "Dan Langille" <[EMAIL PROTECTED]> writes: > > Without leaving "enable_hashjoin = false", can you suggest a way to > > force the index usage? > > Have you tried reducing random_page_cost? Yes. No effect. > FYI, 8.2 should be a bit better about this.

Re: [PERFORM] Forcing index usage without 'enable_hashjoin = FALSE'

2006-08-23 Thread Tom Lane
"Dan Langille" <[EMAIL PROTECTED]> writes: > Without leaving "enable_hashjoin = false", can you suggest a way to > force the index usage? Have you tried reducing random_page_cost? FYI, 8.2 should be a bit better about this. regards, tom lane ---(

Re: [PERFORM] Forcing index usage without 'enable_hashjoin = FALSE'

2006-08-23 Thread Dan Langille
On 23 Aug 2006 at 13:31, Chris wrote: > Dan Langille wrote: > > I'm using PostgreSQL 8.1.4 and I'm trying to force the planner to use > > an index. With the index, I get executions times of 0.5 seconds. > > Without, it's closer to 2.5 seconds. > > > > Compare these two sets of results (also pro

Re: [PERFORM] Which benchmark to use for testing FS?

2006-08-23 Thread Michael Stone
On Wed, Aug 23, 2006 at 03:23:03PM -0700, Jeff Davis wrote: Also, do ext2 or UFS without soft updates run the risk of losing or corrupting my data? I suggest you check the list archives; there's a lot of stuff about filesystems and disk configuration in there. Mike Stone ---

Re: [PERFORM] Query tuning

2006-08-23 Thread Dave Dutcher
It seems to me that what would work best is an index scan backward on the eventtime index. I don't see why that wouldn't work for you, maybe the planner is just esitmating the seq scan and sort is faster for some reason. What does EXPLAIN say if you use a small limit and offset like 10? Or what d

[PERFORM] Which benchmark to use for testing FS?

2006-08-23 Thread Jeff Davis
I am planning to test various filesystems on some new hardware I'm getting. Is pgbench a good way to try out the filesystem? I'm currently planning to test some or all of: Linux: ext2, ext3, XFS, JFS, reiser3, reiser4 FreeBSD: UFS, UFS+SU So, I'm looking for a good way to test just the filesystem

Re: [PERFORM] Query tuning

2006-08-23 Thread Subbiah, Stalin
I get the same plan after running vacuum analyze. Nope, I don't have index on objdomainid, objid and userdomainid. Only eventime has it. -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 22, 2006 8:06 PM To: Subbiah, Stalin Cc: pgsql-performance@postgresql.org

Re: [PERFORM] VACUUM FULL needed sometimes to prevent transaction

2006-08-23 Thread Heikki Linnakangas
Markus Schaber wrote: I just asked myself whether a 2-phase-commit transaction that was prepared, but never committed, can block vacuuming and TID recycling. Yes. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--

Re: [PERFORM] VACUUM FULL needed sometimes to prevent transaction

2006-08-23 Thread Markus Schaber
Hi, Jeff & all, Jeff Davis wrote: > (2) You have a long-running transaction that never completed for some > strange reason. I just asked myself whether a 2-phase-commit transaction that was prepared, but never committed, can block vacuuming and TID recycling. Markus -- Markus Schaber | Logic