Re: [GENERAL] Using the wrong index (very suboptimal), why?

2008-12-23 Thread Shane Wright
Tom, You need to look into what the estimated vs actual rowcounts are for just the two-column condition (ie, where nid = something AND iid1 = something). Getting those estimates to match reality is the key to getting a sane plan choice here. Many thanks - I agree in principle it isn't

Re: [GENERAL] Using the wrong index (very suboptimal), why?

2008-12-23 Thread Shane Wright
Hi Tom, - increasing the statistics target to the maximum setting with SET STATISTICS 1000 on columns rid, nid and iid1 of answers, then re-vacuuming. I hope you meant re-analyzing. Hehe absolutely yes; I always VACUUM VERBOSE ANALYSE :) Index Scan using index_answers_nidiid1 on answers

Re: [GENERAL] Using the wrong index (very suboptimal), why?

2008-12-23 Thread Shane Wright
Tom, Index Scan using index_answers_nidiid1 on answers (cost=0.00..114.14 rows=1 width=60) (actual time=0.206..2398.645 rows=21891 loops=1) Index Cond: ((nid = 253869913) AND (iid1 = 535292129)) Total runtime: 2424.769 ms Well, here's the problem all right: 1 row estimated vs 21891

[GENERAL] Using the wrong index (very suboptimal), why?

2008-12-22 Thread Shane Wright
Hi, I have somewhat of a quandary with a large table in my database; PostgreSQL is choosing the 'wrong' index for a certain kind of query; causing performance to become an order of magnitude slower (query times usually measured in milliseconds now become seconds/minutes!). It's not that it isn't

[GENERAL] Upgrade to 8.3.0?

2008-02-29 Thread Shane Wright
Hi, I'm considering an upgrade to 8.3.0 for some internal databases - would normally wait until at least .1 release of anything for safety but there's a lot of nice sounding stuff here! After trawling the bug logs and doing some testing on some servers here all looks ok except for this bug

Re: [GENERAL] Upgrade to 8.3.0?

2008-02-29 Thread Shane Wright
regards Shane From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Fri 29/02/2008 19:46 To: Shane Wright Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Upgrade to 8.3.0? Shane Wright writes: - Is the HOT/VACUUMFULL bug above dangerous or is its

Re: [GENERAL] Upgrade to 8.3.0?

2008-02-29 Thread Shane Wright
To: Shane Wright Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Upgrade to 8.3.0? Shane Wright [EMAIL PROTECTED] writes: I'm assuming that the default RPMs for RHEL 4 (on ftp.postgresql.org) are not built with --enable-cassert - and that I can veryify this by using the same reproduction

Re: [GENERAL] recovery from xid wraparound

2006-10-24 Thread Shane Wright
) Many thanks for your help! S -Original Message- From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] Sent: 24 October 2006 11:50 To: Shane Wright Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] recovery from xid wraparound On Tue, Oct 24, 2006 at 11:06:01AM +0100, Shane Wright

Re: [GENERAL] recovery from xid wraparound

2006-10-24 Thread Shane Wright
2006 15:23 To: Shane Wright Cc: Martijn van Oosterhout; pgsql-general@postgresql.org Subject: Re: [GENERAL] recovery from xid wraparound Shane Wright [EMAIL PROTECTED] writes: Just make sure you've really covered *all* the system tables. I've been under the impression system tables get done

[GENERAL] recovery from xid wraparound

2006-10-24 Thread Shane Wright
Hi I'm running 7.4 on RHAS 4, and I think I've had a transaction id wraparound issue. Running the command below gives the suitably worrying negative number: emystery=# SELECT datname, age(datfrozenxid) FROM pg_database; datname | age --+- [maindbname]

Re: [GENERAL] mount -o async - is it safe?

2006-01-20 Thread Shane Wright
Hi Tom, If we turn sync off, surely PostgreSQL keeps the data consistent, ext3 journalling keeps the filesystem clean [assuming other mount options left at defaults], and then everything should be ok with either a server crash, power failure, storage failure, whatever. right? I

[GENERAL] mount -o async - is it safe?

2006-01-19 Thread Shane Wright
Hi, We've recently set up our database (7.4.9) with our new hosting provider. We have two database servers running RHEL 4 in a cluster; one active and one hot-spare. They share a [fibre-channel connected] SAN partition; the active server has it mounted. Now my question is this; the provider

Re: [GENERAL] mount -o async - is it safe?

2006-01-19 Thread Shane Wright
Hi, thanks :) If -o async means all I/O is asyncronous except stuff explicitly fsync()ed you're fine. Otherwise... That's the way it works. Async is the default setting for most filesystems, but fsync() is always honored, at last as far as non-lying hardware will allow. :) That sounds

[GENERAL] weird quote bug

2003-06-30 Thread Shane Wright
Hi This is really driving me silly - I can't work it out, can anyone see what I'm doing thats stupid and causing this not to match? This shows that the row exists in the table: emystery= select aid,useragent from useragent where useragent like '%ntserver-ps%'; aid|

Re: [GENERAL] weird quote bug

2003-06-30 Thread Shane Wright
Should have said - I'm using postgreSQL 7.3.3 on Gentoo Linux. The problem occurs both through psql and through PHP4.3.3. Cheers Shane On Monday 30 Jun 2003 12:25 pm, Shane Wright wrote: Hi This is really driving me silly - I can't work it out, can anyone see what I'm doing thats stupid

Re: [GENERAL] weird quote bug

2003-06-30 Thread Shane Wright
They are identical! I can't work out whats going on! Please, if anyone can see what's wrong it'll stop me careering into my early grave! I've never tried this but the docs for LIKE (secfion 6.6.1 in the 7.3 docs) say that to match a literal \ you need to type . An alternative might

[GENERAL] backend stalls

2001-09-27 Thread Shane Wright
Hi I'm tearing my hair out here, the backend seems to randomly decide to stall and not serve any more queries (processes are listed as waiting). It happens on all sorts of queries (including CREATE TABLE and CREATE INDEX). I have completely uninstalled and deleted the whole installation and