From: Gavin Sherry <swm@linuxworld.com.au>
To: Bernd <bernd_pg@genedata.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Select with qualified join condition / Batch inserts
Date: Fri, 15 Oct 2004 20:47:56 +1000 (EST)
Message-ID: <Pine.LNX.4.58.0410152043320.30125@linuxworld.com.au> (raw)
In-Reply-To: <200410151225.26083.bernd_pg@genedata.com>
References: <200410151225.26083.bernd_pg@genedata.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: swm@linuxworld.com.au, bernd_pg@genedata.com
Subject: Re: Select with qualified join condition / Batch inserts
In-Reply-To: <Pine.LNX.4.58.0410152043320.30125@linuxworld.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox