Re: [PERFORM] int4 in a GiST index

2004-11-10 Thread Mike Rylander
On Wed, 10 Nov 2004 18:50:28 -0800 (PST), George Essig <[EMAIL PROTECTED]> wrote: > Mike Rylander wrote: > > > I want to create a multi-column index across the tsvector and classification > > columns as that should help with selectivity. But because there is no > > GiST opclass for INT4 the index

Re: [PERFORM] int4 in a GiST index

2004-11-10 Thread George Essig
Mike Rylander wrote: > I want to create a multi-column index across the tsvector and classification > columns as that should help with selectivity. But because there is no > GiST opclass for INT4 the index creation complains thusly: Install contrib/btree_gist along with contrib/tsearch2 to creat

[PERFORM] int4 in a GiST index

2004-11-10 Thread Mike Rylander
Hello all, I am using tsearch2 to (imagine this... :) index a text field. There is also a, for lack of a better name, "classification" field called 'field' that will be used to group certain rows together. CREATE TABLE biblio.metarecord_field_entry ( record BIGINT REFERENCES bi

[PERFORM] Solaris 9 Tuning Tips requested

2004-11-10 Thread Rod Taylor
I'm looking for suggestions on tuning Solaris 9 for a SunFire 890 (Ultra IV chips) connected to an Hitachi 9500V running PostgreSQL 7.4. Database is approx 160GB in size with a churn of around 4GB per day (2 GB updated, 2GB inserted, very little removed). It's a mixture of OLTP and reporting. 5%

Re: [PERFORM] seqscan strikes again

2004-11-10 Thread Jim C. Nasby
Which column would you recommend? Did something stick out at you? On Tue, Nov 09, 2004 at 03:14:36PM -0800, Joshua D. Drake wrote: > > >opensims=# > > > >I'd really like to avoid putting a 'set enable_seqscan=false' in my > >code, especially since this query only has a problem if it's run on a >

Re: [PERFORM] How to speed-up inserts with jdbc

2004-11-10 Thread Leeuw van der, Tim
Hi, Try using parametrized prepared statements, does that make a difference? Or does PGSQL jdbc not support them in your version? --Tim -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Michael Kleiser Sent: Wednesday, November 10, 2004 2:52 PM To: Jeff Cc:

Re: [PERFORM] How to speed-up inserts with jdbc

2004-11-10 Thread Dave Cramer
couple of things 1) That is a fairly old version of postgres, there are considerable performance improvements in the last 2 releases since, and even more in the pending release. 2) If you are going to insert more rows than that, consider dropping the index before, and recreating after the insert

Re: [PERFORM] How to speed-up inserts with jdbc

2004-11-10 Thread Jeff
On Nov 10, 2004, at 8:51 AM, Michael Kleiser wrote: It is trunning in in 10 Threads. Each thread makes 100 Inserts: For the 1000 Inserts (10 threads a 100 inserts) we need 8 seconds. That's 125 Insets / Seconds. How could we make it faster ? Batch the inserts up into a transaction. So you'd have BE

[PERFORM] How to speed-up inserts with jdbc

2004-11-10 Thread Michael Kleiser
Im PostgreSQL 7.2.2 / Linux 2.4.27 dual-processor Pentium III 900MHz, we have this table: create table testtable (id SERIAL PRIMARY KEY, coni VARCHAR(255), date TIMESTAMP, direction VARCHAR(255), partner VARCHAR(255), type VARCHAR(255), block VARCHAR(255) ); We using Java with JDBC-driver pg72jd

Re: [PERFORM] Need advice on postgresql.conf settings

2004-11-10 Thread Jeff
On Nov 9, 2004, at 2:01 PM, Shane | SkinnyCorp wrote: Thanks in advance for anything you can do to help. The real issue is this, we have THE SAME queries taking anywhere from .001 - 90.0 seconds... the server is using 98% of the available RAM at all times (because of the persistant connections vi

Re: [PERFORM] simple select-statement takes more than 25 sec

2004-11-10 Thread Steinar H. Gunderson
On Wed, Nov 10, 2004 at 12:22:17PM +0100, Cao Duy wrote: > there is an index on customer_id > > create table customer( > ... > CONSTRAINT customer_pkey PRIMARY KEY (customer_id), > ... > ) Oh, sorry, I missed it among all the foreign keys. :-) Anyhow, as others have pointed out, try doing a selec

Re: [PERFORM] simple select-statement takes more than 25 sec

2004-11-10 Thread Cao Duy
Am Mi, den 10.11.2004 schrieb Steinar H. Gunderson um 11:17: > On Wed, Nov 10, 2004 at 10:35:50AM +0100, Cao Duy wrote: > > here is my simple select-statement: > > SELECT * FROM CUSTOMER WHERE CUSTOMER_ID=5 > > It seems like you're missing an index on customer_id. Set it to PRIMARY KEY > or do an

Re: [PERFORM] simple select-statement takes more than 25 sec

2004-11-10 Thread gnari
From: "Cao Duy" <[EMAIL PROTECTED]> > > here is my simple select-statement: > SELECT * FROM CUSTOMER WHERE CUSTOMER_ID=5 > > the result appears after about 27 sec. > > what's wrong? > ... > CREATE TABLE public.customer > ( > customer_id bigserial NOT NULL, you do not specify version or show u

Re: [PERFORM] simple select-statement takes more than 25 sec

2004-11-10 Thread Steinar H. Gunderson
On Wed, Nov 10, 2004 at 10:35:50AM +0100, Cao Duy wrote: > here is my simple select-statement: > SELECT * FROM CUSTOMER WHERE CUSTOMER_ID=5 It seems like you're missing an index on customer_id. Set it to PRIMARY KEY or do an explicit CREATE INDEX (followed by an ANALYZE) and it should be a lot fas

[PERFORM] simple select-statement takes more than 25 sec

2004-11-10 Thread Cao Duy
Hi all I have a table with ca. 4Mio Rows. here is my simple select-statement: SELECT * FROM CUSTOMER WHERE CUSTOMER_ID=5 the result appears after about 27 sec. what's wrong? the same statement on mysql takes 1 milisec. please help here is the structur of the table CREATE TABLE public.custome