On Fri, 15 Oct 2004, Bernd wrote: > Hi, > > we are working on a product which was originally developed against an Oracle > database and which should be changed to also work with postgres. > > Overall the changes we had to make are very small and we are very pleased with > the good performance of postgres - but we also found queries which execute > much faster on Oracle. Since I am not yet familiar with tuning queries for > postgres, it would be great if someone could give me a hint on the following > two issues. (We are using PG 8.0.0beta3 on Linux kernel 2.4.27): > > 1/ The following query takes about 5 sec. with postrgres whereas on Oracle it > executes in about 30 ms (although both tables only contain 200 k records in > the postgres version). > > SQL: > > SELECT cmp.WELL_INDEX, cmp.COMPOUND, con.CONCENTRATION > FROM SCR_WELL_COMPOUND cmp, SCR_WELL_CONCENTRATION con > WHERE cmp.BARCODE=con.BARCODE > AND cmp.WELL_INDEX=con.WELL_INDEX > AND cmp.MAT_ID=con.MAT_ID > AND cmp.MAT_ID = 3 > AND cmp.BARCODE='910125864' > AND cmp.ID_LEVEL = 1; > > Table-def: > Table "public.scr_well_compound" > Column | Type | Modifiers > ------------+------------------------+----------- > mat_id | numeric(10,0) | not null > barcode | character varying(240) | not null > well_index | numeric(5,0) | not null > id_level | numeric(3,0) | not null > compound | character varying(240) | not null > Indexes: > "scr_wcm_pk" PRIMARY KEY, btree (id_level, mat_id, barcode, well_index)
I presume you've VACUUM FULL'd and ANALYZE'd? Can we also see a plan? EXPLAIN ANALYZE <query>. http://www.postgresql.org/docs/7.4/static/sql-explain.html. You may need to create indexes with other primary columns. Ie, on mat_id or barcode. > 2/ Batch-inserts using jdbc (maybe this should go to the jdbc-mailing list - > but it is also performance related ...): > Performing many inserts using a PreparedStatement and batch execution makes a > significant performance improvement in Oracle. In postgres, I did not observe > any performance improvement using batch execution. Are there any special > caveats when using batch execution with postgres? The JDBC people should be able to help with that. Gavin ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
