Re: slow database - Mailing list pgsql-performance

First thing I would check is to make sure all those foreign keys are the
same type.

Second, make sure you've got indexes to go with them.  I.e. on a multi-key
fk, have a multi-key index.


On Wed, 11 Feb 2004 alemon@tiago.hazor.com.br wrote:

> the version is 7.3.2 in a connective 9.
> the hen foot is without nails at the moment: =)
>  |
> /|\
>
> this is a principal table of system:
>
> CREATE TABLE public.compra_prod_forn
> (
>   nu_seq_prod_forn numeric(12) NOT NULL,
>   cd_fabricante numeric(6),
>   cd_moeda numeric(4) NOT NULL,
>   cd_compra numeric(12) NOT NULL,
>   cd_produto numeric(9),
>   cd_fornecedor numeric(6),
>   cd_incotermes numeric(3) NOT NULL,
>   qtde_compra numeric(12,3),
>   perc_comissao_holding numeric(5,2),
>   vl_cotacao_unit_negociacao numeric(20,3),
>   dt_retorno date,
>   cd_status_cv numeric(3) NOT NULL,
>   cd_usuario numeric(6) NOT NULL,
>   tp_comissao varchar(25),
>   vl_pif numeric(9,2),
>   cd_fornecedor_contato numeric(6),
>   cd_contato numeric(6),
>   cd_un_peso varchar(20),
>   vl_currier numeric(9,2),
>   cd_iqf numeric(3),
>   cd_un_peso_vl_unit varchar(10),
>   dt_def_fornecedor date,
>   vl_cotacao_unit_forn numeric(20,3),
>   vl_cotacao_unit_local numeric(20,3),
>   tp_vl_cotacao_unit numeric(1),
>   cd_moeda_forn numeric(4),
>   cd_moeda_local numeric(4),
>   vl_cotacao_unit numeric(20,3),
>   peso_bruto_emb varchar(20),
>   id_fax numeric(1),
>   id_email numeric(1),
>   fob varchar(40),
>   origem varchar(40),
>   tipo_comissao varchar(40),
>   descr_fabricante_select varchar(200),
>   farmacopeia varchar(100),
>   vl_frete numeric(10,3),
>   descr_abandono_representada varchar(2000),
>   descr_abandono_interno varchar(2000),
>   vl_frete_unit numeric(10,3),
>   CONSTRAINT compra_prod_forn_pkey PRIMARY KEY (cd_compra, nu_seq_prod_forn),
>   CONSTRAINT "$1" FOREIGN KEY (cd_moeda_local) REFERENCES public.moeda
> (cd_moeda) ON UPDATE NO ACTION ON DELETE NO ACTION,
>   CONSTRAINT "$10" FOREIGN KEY (cd_usuario) REFERENCES public.usuario_sistema
> (cd_usuario) ON UPDATE NO ACTION ON DELETE NO ACTION,
>   CONSTRAINT "$11" FOREIGN KEY (cd_status_cv) REFERENCES
> public.status_compra_venda (cd_status_cv) ON UPDATE NO ACTION ON DELETE NO
> ACTION,
>   CONSTRAINT "$12" FOREIGN KEY (cd_moeda) REFERENCES public.moeda (cd_moeda) ON
> UPDATE NO ACTION ON DELETE NO ACTION,
>   CONSTRAINT "$2" FOREIGN KEY (cd_moeda_forn) REFERENCES public.moeda
> (cd_moeda) ON UPDATE NO ACTION ON DELETE NO ACTION,
>   CONSTRAINT "$3" FOREIGN KEY (cd_un_peso_vl_unit) REFERENCES
> public.unidades_peso (cd_un_peso) ON UPDATE NO ACTION ON DELETE NO ACTION,
>   CONSTRAINT "$4" FOREIGN KEY (cd_un_peso) REFERENCES public.unidades_peso
> (cd_un_peso) ON UPDATE NO ACTION ON DELETE NO ACTION,
>   CONSTRAINT "$5" FOREIGN KEY (cd_fornecedor_contato, cd_contato) REFERENCES
> public.fornecedor_contato (cd_fornecedor, cd_contato) ON UPDATE NO ACTION ON
> DELETE NO ACTION,
>   CONSTRAINT "$6" FOREIGN KEY (cd_fabricante) REFERENCES public.fabricante
> (cd_fabricante) ON UPDATE NO ACTION ON DELETE NO ACTION,
>   CONSTRAINT "$7" FOREIGN KEY (cd_produto, cd_fornecedor) REFERENCES
> public.fornecedor_produto (cd_produto, cd_fornecedor) ON UPDATE NO ACTION ON
> DELETE NO ACTION,
>   CONSTRAINT "$8" FOREIGN KEY (cd_incotermes) REFERENCES public.incotermes
> (cd_incotermes) ON UPDATE NO ACTION ON DELETE NO ACTION,
>   CONSTRAINT "$9" FOREIGN KEY (cd_compra) REFERENCES public.compra (cd_compra)
> ON UPDATE NO ACTION ON DELETE CASCADE
> ) WITH OIDS;
>
>
>
> Quoting "scott.marlowe" <scott.marlowe@ihs.com>:
>
> > On Wed, 11 Feb 2004 alemon@tiago.hazor.com.br wrote:
> >
> > > I already came back the old conditions and I continue slow in the same
> > > way!
> >
> > OK, we need some things from you to help troubleshoot this problem.
> >
> > Postgresql version
> > schema of your tables
> > output of "explain analyze your query here"
> > a chicken foot (haha, just kidding. :-)
> >
> >
>
>
>
>