pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (7+ messages)  latest in thread

Message-ID: <Pine.LNX.4.58.0410152043320.30125@linuxworld.com.au>
Permalink:  ../Pine.LNX.4.58.0410152043320.30125@linuxworld.com.au/
Also on:    postgresql.org/message-id/Pine.LNX.4.58.0410152043320.30125@linuxworld.com.au

 · 

reply

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