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