Re: Optimization of this SQL sentence - Mailing list pgsql-performance

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1




Indexes in comment
Comment rows: 17.250

CREATE INDEX usuariofichaoncommnet
  ON "comment"
  USING btree
  (idusuarioficha);

Comment structure:
CREATE TABLE "comment"
(
  idcomment int4 NOT NULL DEFAULT
nextval('comment_idcomment_seq'::regclass),
  score int4,
  title varchar,
  ctext varchar,
  idusuarioficha int4,
  galleta varchar,
  navlang int4,
  cdate timestamp DEFAULT now(),
  idstatus int4,
  ctype int4 NOT NULL,
  idfile int4 NOT NULL,
  nick varchar,
  nombre varchar,
  apellidos varchar,
  dni varchar,
  nacionalidad varchar,
  email varchar,
  telefono varchar,
  code varchar,
  memo varchar,
  c_ip varchar(30),
  codpais char(2),
  replay varchar,
  replaydate timestamp,
  advsent int4,
  usrwarn int4,
  nouserlink int4,
  aviso_confirmacion_15 timestamp,
  aviso_confirmacion_60 timestamp,
  CONSTRAINT comment_pkey PRIMARY KEY (idcomment)
)

Ficha structure:
No indexes in ficha
Ficha rows: 17.850

CREATE TABLE ficha
(
  idficha int4 NOT NULL DEFAULT nextval('ficha_idficha_seq'::regclass),
  email varchar(255),
  web varchar(255),
  capacidadmin int4,
  capacidadmax int4,
  preciotb float4,
  preciota float4,
  cp varchar(20),
  telefono1 varchar(50),
  telefono2 varchar(50),
  fax varchar(50),
  uprecio varchar,
  udireccion varchar(512),
  comentarios varchar,
  ucapacidad varchar(512),
  upresentacion varchar,
  utipoaloj varchar(50),
  ulugares varchar,
  ucaracteristica varchar,
  idusuario int4,
  idlocacion int4,
  contacto varchar(255),
  fuente varchar(512),
  prefijopais varchar(10),
  idestado char(1),
  nombre varchar(255),
  idtipoalojamiento int4,
  ulocalidad varchar(255),
  creado timestamp DEFAULT now(),
  cachefault int4 DEFAULT 0,
  idpromotiontype_pc int4 NOT NULL DEFAULT 0,
  idpromotiontype_ant_pc int4,
  promostartdate_pc timestamp,
  promoenddate_pc timestamp,
  localidadruta varchar(255),
  urlsufix varchar(32),
  searchengine1 int4,
  searchengine2 int4,
  searchengine3 int4,
  searchengine4 int4,
  searchengine5 int4,
  searchengine6 int4,
  deseo1 int4,
  deseo2 int4,
  deseo3 int4,
  deseo4 int4,
  deseo5 int4,
  deseo6 int4,
  otherspecs varchar(510),
  lastchange timestamp,
  idsubestado int4,
  environment int4,
  prefijopais2 varchar,
  web_agencia varchar(255),
  lat varchar(25),
  long varchar(25),
  zoom int4,
  swzoombloq bool DEFAULT true,
  titulomapa_l0 varchar(255),
  titulomapa_l1 varchar(255),
  titulomapa_l2 varchar(255),
  titulomapa_l3 varchar(255),
  titulomapa_l4 varchar(255),
  titulomapa_l5 varchar(255),
  titulomapa_l6 varchar(255),
  titulomapa_l7 varchar(255),
  titulomapa_l8 varchar(255),
  titulomapa_l9 varchar(255),
  CONSTRAINT pk_ficha PRIMARY KEY (idficha),
  CONSTRAINT fk_ficha_geonivel6 FOREIGN KEY (idlocacion) REFERENCES
geonivel6 (idgeonivel6) ON UPDATE NO ACTION ON DELETE NO ACTION
)



Gregory S. Williamson escribió:
> Off hanbd I can't recommend anything, bur perhaps you could post the details of the tables (columns, indexes),and
someinfo on what version of postgres you are using. 
>
> Are the tables recently analyzed ? How many rows in them ?
>
> Greg Williamson
> DBA
> GlobeXplorer LLC
>
>
> -----Original Message-----
> From:    pgsql-performance-owner@postgresql.org on behalf of Ruben Rubio
> Sent:    Tue 10/17/2006 2:05 AM
> To:    pgsql-performance@postgresql.org
> Cc:
> Subject:    [PERFORM] Optimization of this SQL sentence
>
> This SQL sentence is very simple. I need to get better results. I have
> tried some posibilities and I didn't get good results.
>
> SELECT max(idcomment)
>  FROM ficha vf
>  INNER JOIN comment c ON (vf.idficha=c.idfile AND (idestado=3 OR
> idestado=4))
>  WHERE idstatus=3
>  AND ctype=1
>
>
> QUERY PLAN
>
> Aggregate  (cost=2730.75..2730.76 rows=1 width=4) (actual
> time=188.463..188.469 rows=1 loops=1)
>
>   ->  Hash Join  (cost=1403.44..2730.72 rows=11 width=4) (actual
> time=141.464..185.404 rows=513 loops=1)
>
>         Hash Cond: ("outer".idfile = "inner".idficha)
>
>         ->  Seq Scan on "comment" c  (cost=0.00..1321.75 rows=1083
> width=8) (actual time=0.291..36.112 rows=642 loops=1)
>
>               Filter: ((idstatus = 3) AND (ctype = 1))
>
>         ->  Hash  (cost=1403.00..1403.00 rows=178 width=4) (actual
> time=141.004..141.004 rows=6282 loops=1)
>
>               ->  Seq Scan on ficha vf  (cost=0.00..1403.00 rows=178
> width=4) (actual time=0.071..97.885 rows=6282 loops=1)
>
>                     Filter: (((idestado)::text = '3'::text) OR
> ((idestado)::text = '4'::text))
>
> Total runtime: 188.809 ms
>
>
> Thanks in advance,
> Ruben Rubio

- ---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq


- -------------------------------------------------------
Click link below if it is SPAM gsw@globexplorer.com

"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=45349c86275246672479766&user=gsw@globexplorer.com&retrain=spam&template=history&history_page=1"
!DSPAM:45349c86275246672479766!
- -------------------------------------------------------








-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFFNKNeIo1XmbAXRboRAsiDAKCce+BeyYK63r24w2E1QNq/3maMJQCeNpNw
GiwJ/KixMHH76919wQR31g8=
=g/re
-----END PGP SIGNATURE-----