Re: Select with qualified join condition / Batch inserts - Mailing list pgsql-performance

On Fri, 15 Oct 2004 08:47 pm, Gavin Sherry wrote:
> On Fri, 15 Oct 2004, Bernd wrote:
>
> > Hi,
[snip]

> > 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)
>
numeric is not optimized by postgresql like it is by Oracle.  You will get much better
performance by changing the numeric types to int, big int, or small int.

That should get the query time down to somewhere near what Oracle is giving you.

Regards

Russell Smith.



[snip]