Re: [PERFORM] For full text indexing, which is better, tsearch2 or

2003-11-26 Thread Steve Atkins
On Thu, Nov 27, 2003 at 12:41:59PM +0800, Christopher Kings-Lynne wrote: > >Does anyone have any metrics on how fast tsearch2 actually is? > > > >I tried it on a synthetic dataset of a million documents of a hundred > >words each and while insertions were impressively fast I gave up on > >the searc

Re: [PERFORM] For full text indexing, which is better, tsearch2 or

2003-11-26 Thread Christopher Kings-Lynne
Does anyone have any metrics on how fast tsearch2 actually is? I tried it on a synthetic dataset of a million documents of a hundred words each and while insertions were impressively fast I gave up on the search after 10 minutes. Broken? Unusable slow? This was on the last 7.4 release candidate. I

Re: [PERFORM] For full text indexing, which is better, tsearch2 or

2003-11-26 Thread Steve Atkins
On Thu, Nov 27, 2003 at 08:51:14AM +0800, Christopher Kings-Lynne wrote: > >Which one is better (performance/easier to use), > >tsearch2 or fulltextindex? > >there is an example how to use fulltextindex in the > >techdocs, but I checked the contrib/fulltextindex > >package, there is a WARNING that

Re: [PERFORM] For full text indexing, which is better, tsearch2 or

2003-11-26 Thread Dror Matalon
On Thu, Nov 27, 2003 at 08:51:14AM +0800, Christopher Kings-Lynne wrote: > >Which one is better (performance/easier to use), > >tsearch2 or fulltextindex? > >there is an example how to use fulltextindex in the > >techdocs, but I checked the contrib/fulltextindex > >package, there is a WARNING tha

Re: [PERFORM] very large db performance question

2003-11-26 Thread Christopher Kings-Lynne
Thanks for reply. Actually our database only supply some scientists to use (we predict that). so there is no workload problem. there is only very infrequent updates. the query is not complex. the problem is, we have one table that store most of the data ( with 200 million rows). In this table, ther

Re: [PERFORM] For full text indexing, which is better, tsearch2 or

2003-11-26 Thread Christopher Kings-Lynne
Which one is better (performance/easier to use), tsearch2 or fulltextindex? there is an example how to use fulltextindex in the techdocs, but I checked the contrib/fulltextindex package, there is a WARNING that fulltextindex is much slower than tsearch2. but tsearch2 seems complex to use, and I ca

Re: [PERFORM] Followup - expression (functional) index use in joins

2003-11-26 Thread Tom Lane
Roger Ging <[EMAIL PROTECTED]> writes: > Ran vacuum analyse on both program and logfile tables. Estimates are > more in line with reality now, And they are what now? You really can't expect to get useful help here when you're being so miserly with the details ... FWIW, I suspect you could forc

Re: [PERFORM] very large db performance question

2003-11-26 Thread LIANHE SHAO
Thanks for reply. Actually our database only supply some scientists to use (we predict that). so there is no workload problem. there is only very infrequent updates. the query is not complex. the problem is, we have one table that store most of the data ( with 200 million rows). In this table, ther

Re: [PERFORM] cross table indexes or something?

2003-11-26 Thread Hannu Krosing
Jeremiah Jahn kirjutas K, 26.11.2003 kell 22:14: > I was wondering if there is something I can do that would act similar to > a index over more than one table. > > I have about 3 million people in my DB at the moment, they all have > roles, and many of them have more than one name. > > for exam

Re: [PERFORM] cross table indexes or something?

2003-11-26 Thread Marc A. Leith
Sybase IQ lets you build "joined indexsets". This is amazing but pricey and really intended more for Data Warehousing than OLTP, although they did release a version which permitted writes on-the-fly. (This was implemented using a multi-concurrency solution much like PostreSQL uses.) It essential

Re: [PERFORM] very large db performance question

2003-11-26 Thread Neil Conway
LIANHE SHAO <[EMAIL PROTECTED]> writes: > We will have a very large database to store microarray data (may > exceed 80-100G some day). now we have 1G RAM, 2G Hz Pentium 4, 1 > CPU. and enough hard disk. > Could anybody tell me that our hardware is an issue or not? IMHO the size of the DB is less

Re: [PERFORM] Followup - expression (functional) index use in joins

2003-11-26 Thread Roger Ging
Ran vacuum analyse on both program and logfile tables.  Estimates are more in line with reality now, but query still takes 10 seconds on v7.4 and 10 ms on v7.3.  Function is marked as immutable and returns varchar(5).  I am wondering why the planner would choose a merge join (v7.4) as opposed t

[PERFORM] cross table indexes or something?

2003-11-26 Thread Jeremiah Jahn
I was wondering if there is something I can do that would act similar to a index over more than one table. I have about 3 million people in my DB at the moment, they all have roles, and many of them have more than one name. for example, a Judge will only have one name, but a Litigant could have

[PERFORM] very large db performance question

2003-11-26 Thread LIANHE SHAO
Hello All, We will have a very large database to store microarray data (may exceed 80-100G some day). now we have 1G RAM, 2G Hz Pentium 4, 1 CPU. and enough hard disk. I never touched such large database before. I ask several dbas if the hardware is ok, some said it is ok for the query, but I am

[PERFORM] For full text indexing, which is better, tsearch2 or fulltextindex

2003-11-26 Thread LIANHE SHAO
Hi all, Which one is better (performance/easier to use), tsearch2 or fulltextindex? there is an example how to use fulltextindex in the techdocs, but I checked the contrib/fulltextindex package, there is a WARNING that fulltextindex is much slower than tsearch2. but tsearch2 seems complex to use,

Re: [PERFORM] Followup - expression (functional) index use in joins

2003-11-26 Thread Richard Huxton
On Wednesday 26 November 2003 18:39, Roger Ging wrote: > version 7.4 results: > > explain analyse SELECT L.row_id FROM music.logfile L LEFT JOIN > music.program P ON > music.fn_mri_id_no_program(P.mri_id_no) = L.program_id > WHERE L.station = UPPER('kabc')::VARCHAR > AND L.air_date = '04/12/2002':

Re: [PERFORM] Maximum Possible Insert Performance?

2003-11-26 Thread Dror Matalon
But the permissions of the base ramdisk might be wrong. I'd su to the user that you run postgres as (probably postgres), and make sure that you can go to the directory where the log and the database files are and make sure you can see the files. On Wed, Nov 26, 2003 at 10:03:47AM -0800, William Y

[PERFORM] Followup - expression (functional) index use in joins

2003-11-26 Thread Roger Ging
version 7.4 results: explain analyse SELECT L.row_id FROM music.logfile L LEFT JOIN music.program P ON music.fn_mri_id_no_program(P.mri_id_no) = L.program_id WHERE L.station = UPPER('kabc')::VARCHAR AND L.air_date = '04/12/2002'::TIMESTAMP AND P.cutoff_date IS NULL ORDER BY L.chron_start,L.chron

Re: [PERFORM] Maximum Possible Insert Performance?

2003-11-26 Thread William Yu
Tom Lane wrote: William Yu <[EMAIL PROTECTED]> writes: I then tried to put the WAL directory onto a ramdisk. I turned off swapping, created a tmpfs mount point and copied the pg_xlog directory over. Everything looked fine as far as I could tell but Postgres just panic'd with a "file permissions

Re: [PERFORM] expression (functional) index use in joins

2003-11-26 Thread Richard Huxton
On Wednesday 26 November 2003 16:38, Roger Ging wrote: > I just installed v7.4 and restored a database from v7.3.4. [snip] Hmm - you seem to be getting different row estimates in the plan. Can you re-analyse both versions and post EXPLAIN ANALYSE rather than just EXPLAIN? > -> S

Re: [PERFORM] Maximum Possible Insert Performance?

2003-11-26 Thread Tom Lane
William Yu <[EMAIL PROTECTED]> writes: > I then tried to put the WAL directory onto a ramdisk. I turned off > swapping, created a tmpfs mount point and copied the pg_xlog directory > over. Everything looked fine as far as I could tell but Postgres just > panic'd with a "file permissions" error.

Re: [PERFORM] why index scan not working when using 'like'?

2003-11-26 Thread Hannu Krosing
Tom Lane kirjutas T, 25.11.2003 kell 23:29: > Josh Berkus <[EMAIL PROTECTED]> writes: > > In regular text fields containing words, your problem is solvable with full > > text indexing (FTI). Unfortunately, FTI is not designed for arbitrary > > non-language strings. It could be adapted, but wou

Re: [PERFORM] Impossibly slow DELETEs

2003-11-26 Thread Greg Stark
Is it possible another connection has updated the record and not committed, and it takes a minute for the connection to time out and commit or roll back? -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROT

Re: [PERFORM] Maximum Possible Insert Performance?

2003-11-26 Thread William Yu
Josh Berkus wrote: William, When my current job batch is done, I'll save a copy of the dir and give the WAL on ramdrive a test. And perhaps even buy a Sandisk at the local store and run that through the hooper. We'll be interested in the results. The Sandisk won't be much of a performance te

[PERFORM] expression (functional) index use in joins

2003-11-26 Thread Roger Ging
I just installed v7.4 and restored a database from v7.3.4. I have an index based on a function that the planner is using on the old version, but doing seq scans on left joins in the new version. I have run analyze on the table post restore. the query returns in less than 1 second on version 7