Re: Optimization of this SQL sentence

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



You could try rewriting the query like this:

SELECT MAX(idcomment)
FROM comment c
WHERE idstatus=3 AND ctype=1
AND EXISTS (SELECT 1 FROM ficha vf WHERE idestado IN ('3', '4') AND vf.idficha = c.idfile);

The planner can then try a backward scan on the comment_pkey index, which should be quicker than the seq scan assuming that there's a lot of rows that match the restrictions (idstatus=3 ctype=1 idestado IN ('3', '4')).

But see comments inline below:

Ruben Rubio wrote:
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)
)

Without knowing anything about you're application, it looks like there's a some fields in the comment-table that are duplicates of fields in the ficha-table. Telefono and email for example. You should consider doing some normalization.

No indexes in ficha

Except for the implicit idficha_pkey index.

CREATE TABLE ficha
(
> ...
>   idestado char(1),

If idestado contains numbers (codes of some kind, I presume), you're better off using the smallint data type.

> ....
  searchengine1 int4,
  searchengine2 int4,
  searchengine3 int4,
  searchengine4 int4,
  searchengine5 int4,
  searchengine6 int4,

Normalization?!

  deseo1 int4,
  deseo2 int4,
  deseo3 int4,
  deseo4 int4,
  deseo5 int4,
  deseo6 int4,

For these as well...

> ...
  lat varchar(25),
  long varchar(25),

Isn't there's a better data type for latitude and longitude? Decimal, perhaps?

  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),

Again, normalization...

- Heikki


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux