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)
Foreign-key constraints:
    "scr_wcm_mat_fk" FOREIGN KEY (mat_id) REFERENCES scr_mapping_table(mat_id) 
ON DELETE CASCADE

       Table "public.scr_well_concentration"
    Column     |          Type          | Modifiers
---------------+------------------------+-----------
 mat_id        | numeric(10,0)          | not null
 barcode       | character varying(240) | not null
 well_index    | numeric(5,0)           | not null
 concentration | numeric(20,10)         | not null
Indexes:
    "scr_wco_pk" PRIMARY KEY, btree (mat_id, barcode, well_index)
Foreign-key constraints:
    "scr_wco_mat_fk" FOREIGN KEY (mat_id) REFERENCES scr_mapping_table(mat_id) 
ON DELETE CASCADE

I tried several variants of the query (including the SQL 92 JOIN ON syntax) 
but with no success. I have also rebuilt the underlying indices.

A strange observation is that the same query runs pretty fast without the 
restriction to a certain MAT_ID, i. e. omitting the MAT_ID=3 part.

Also fetching the data for both tables separately is pretty fast and a 
possible fallback would be to do the actual join in the application (which is 
of course not as beautiful as doing it using SQL ;-)

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?

Thanks and regards

Bernd




---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Reply via email to