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]
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
