[PERFORM] simple select-statement takes more than 25 sec
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.customer ( customer_id bigserial NOT NULL, cooperationpartner_id int8 NOT NULL DEFAULT 0::bigint, maincontact_id int8 NOT NULL DEFAULT 0::bigint, companycontact_id int8, def_paymentdetails_id int8, def_paymentsort_id int8, def_invoicing_id int8, int_customernumber varchar(50), ext_customernumber varchar(50), CONSTRAINT customer_pkey PRIMARY KEY (customer_id), CONSTRAINT customer_ibfk_1 FOREIGN KEY (cooperationpartner_id) REFERENCES public.cooperationpartner (cooperationpartner_id) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT customer_ibfk_2 FOREIGN KEY (maincontact_id) REFERENCES public.contact (contact_id) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT customer_ibfk_3 FOREIGN KEY (companycontact_id) REFERENCES public.contact (contact_id) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT customer_ibfk_4 FOREIGN KEY (def_paymentdetails_id) REFERENCES public.paymentdetails (paymentdetails_id) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT customer_ibfk_5 FOREIGN KEY (def_paymentsort_id) REFERENCES public.paymentsort (paymentsort_id) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT customer_ibfk_6 FOREIGN KEY (def_invoicing_id) REFERENCES public.invoicing (invoicing_id) ON UPDATE NO ACTION ON DELETE NO ACTION ) WITH OIDS; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] simple select-statement takes more than 25 sec
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 faster. /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] simple select-statement takes more than 25 sec
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 us an explain analyze, or tell us what indexes you have, but if you want to use an index on the bigint column customer_id, and you are using postgres version 7.4 or less, you need to cast your constant (5) to bigint. try SELECT * FROM CUSTOMER WHERE CUSTOMER_ID=5::bigint or SELECT * FROM CUSTOMER WHERE CUSTOMER_ID='5' gnari ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] simple select-statement takes more than 25 sec
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 explicit CREATE INDEX (followed by an ANALYZE) and it should be a > lot faster. there is an index on customer_id create table customer( ... CONSTRAINT customer_pkey PRIMARY KEY (customer_id), ... ) > /* Steinar */ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] simple select-statement takes more than 25 sec
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 select against 5::bigint instead of just 5 (which is an integer). /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Need advice on postgresql.conf settings
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 via php), and I don't know what to do. Every time I change a I'd recommend strongly ditching the use of pconnect and use pgpool + regular connect. It is a terrific combination that provides pool connections like how you'd think they shoudl work (a pool of N connections to PG shared by Y processes instead of a 1:1 mapping). curiously, have you noticed any pattern to the slowdown? It could be induced by a checkpoint or vacuum. Are you swapping at all? Are your PHP scripts leaking at all, etc.? Your load average is high, how does your CPU idle look (if load is high, and the cpus are pretty idle that is an indicator of being IO bound). good luck. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] How to speed-up inserts with jdbc
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 pg72jdbc2.jar
our Java-testgrogram is :
public class Stresser implements Runnable {
public void run() {
System.out.println("-> start");
try {
Class.forName("org.postgresql.Driver");
Connection con = DriverManager.getConnection("jdbc:postgresql://"+prop.getProperty("host")+":"+prop.getProperty("port")+"/"+prop.getProperty("dbname"), prop.getProperty("user"),
prop.getProperty("pwd"));
con.setAutoCommit(true);
Statement st = con.createStatement();
java.sql.Timestamp datum = new java.sql.Timestamp(new Date().getTime());
Date start = new Date();
System.out.println(start);
for (int i=0; i<100; ++i) {
st.executeUpdate("insert into history(uuid,coni,date,direction,partner,type) values('uuid','content','"+datum+"','dir','partner','type')");
}
Date end = new Date();
System.out.println(end);
con.close();
} catch (Exception e) {
System.out.println("Exception!");
e.printStackTrace();
}
System.out.println("-> ende");
}
public static void main(String[] args) {
for (int i=0; i<10; ++i) {
Stresser s = new Stresser();
Thread t = new Thread(s);
t.start();
}
}
}
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 ?
Inserting 1000 rows via INSERT AS SELECT is much faster.
regards
Michael
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] How to speed-up inserts with jdbc
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 BEGIN insert insert insert ... COMMIT Your numbers will suddenly sky rocket. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] How to speed-up inserts with jdbc
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.
Dave
Michael Kleiser wrote:
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 pg72jdbc2.jar
our Java-testgrogram is :
public class Stresser implements Runnable {
public void run() {
System.out.println("-> start");
try {
Class.forName("org.postgresql.Driver");
Connection con =
DriverManager.getConnection("jdbc:postgresql://"+prop.getProperty("host")+":"+prop.getProperty("port")+"/"+prop.getProperty("dbname"),
prop.getProperty("user"), prop.getProperty("pwd"));
con.setAutoCommit(true);
Statement st = con.createStatement();
java.sql.Timestamp datum = new java.sql.Timestamp(new
Date().getTime());
Date start = new Date();
System.out.println(start);
for (int i=0; i<100; ++i) {
st.executeUpdate("insert into
history(uuid,coni,date,direction,partner,type)
values('uuid','content','"+datum+"','dir','partner','type')");
}
Date end = new Date();
System.out.println(end);
con.close();
} catch (Exception e) {
System.out.println("Exception!");
e.printStackTrace();
}
System.out.println("-> ende");
}
public static void main(String[] args) {
for (int i=0; i<10; ++i) {
Stresser s = new Stresser();
Thread t = new Thread(s);
t.start();
}
}
}
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 ?
Inserting 1000 rows via INSERT AS SELECT is much faster.
regards
Michael
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] How to speed-up inserts with jdbc
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: Shane|SkinnyCorp; [EMAIL PROTECTED]
Subject: [PERFORM] How to speed-up inserts with jdbc
[...]
> Statement st = con.createStatement();
[...]
st.executeUpdate("insert into
history(uuid,coni,date,direction,partner,type)
values('uuid','content','"+datum+"','dir','partner','type')");
[...]
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [PERFORM] seqscan strikes again
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 > >large date/time window, which normally doesn't happen. > > Try increasing your statistics target for the column and then rerunning > analyze. > > Sincerely, > > Joshua D. Drake -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] Solaris 9 Tuning Tips requested
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% is reports which do trickle writes 95% is short (30 second or less) transactions with about 10 selects, 10 writes (inserts, updates, deletes all mixed in) affecting 150 tuples. Thanks for any tips -- particularly Solaris kernel tuning or oddities in Disk IO or configuration settings as they related to Solaris (as they differ from an Intel). -- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] int4 in a GiST index
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 biblio.metarecord (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, field INT NOT NULL REFERENCES biblio.metarecord_field_map (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, value TEXT, value_fti tsvector, source BIGINT NOT NULL REFERENCES biblio.record (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED ) WITHOUT OIDS; Because there will be "or" queries against the 'value_fti' 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: oils=# CREATE INDEX metarecord_field_entry_value_and_field_idx ON biblio.metarecord_field_entry USING GIST (field, value_fti); ERROR: data type integer has no default operator class for access method "gist" HINT: You must specify an operator class for the index or define a default operator class for the data type. I attempted to give it the 'int4_ops' class, but that also complains: oils=# CREATE INDEX metarecord_field_entry_value_and_field_idx ON biblio.metarecord_field_entry USING GIST (value_fti, field int4_ops); ERROR: operator class "int4_ops" does not exist for access method "gist" I couldn't find any info in the docs (7.4 and 8.0.0b4) for getting GiST to index standard integers. I'm sure this has been done before, but I've note found the magic spell. Of course, I may just be barking up the wrong tree altogether... Thanks in advance! -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] int4 in a GiST index
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 create a multicolumn index on the in4 and the tsvector columns. See the following for an example: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/oscon_tsearch2/multi_column_index.html George Essig ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] int4 in a GiST index
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 creation complains thusly: > > Install contrib/btree_gist along with contrib/tsearch2 to create a > multicolumn index on the in4 > and the tsvector columns. See the following for an example: > > http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/oscon_tsearch2/multi_column_index.html > > George Essig > Thanks a million. I had actually just found the answer after some more googling, but I hadn't seen that page and it happens to be exactly what I wanted. As a side note I'd like to thank everyone here (and especially George, in this case). I've been on these lists for quite a while and I'm always amazed at the speed, accuracy and precision of the answers on the PG mailing lists. -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer ---(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
