Re: Select with qualified join condition / Batch inserts

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