thread-index: Acbx0waJhMHQ2NG0RYOfLO6ypMDUkg==
Message-ID: <4534A9B2.8060401@enterprisedb.com>
Content-Class: urn:content-classes:message
Date: Tue, 17 Oct 2006 11:00:18 +0100
Importance: normal
From: "Heikki Linnakangas" <heikki@enterprisedb.com>
User-Agent: Thunderbird 1.5.0.7 (X11/20060915)
MIME-Version: 1.0
To: "Ruben Rubio" <ruben@rentalia.com>
Cc: "Gregory S. Williamson" <gsw@globexplorer.com>,
	<pgsql-performance@postgresql.org>
Subject: Re: Optimization of this SQL sentence
References: <45349CDF.3090002@rentalia.com>
	<71E37EF6B7DCC1499CEA0316A256832802B3E738@loki.wc.globexplorer.net>
	<4534A35E.9030909@rentalia.com>
In-Reply-To: <4534A35E.9030909@rentalia.com>
Content-Type: text/plain;
	format=flowed;
	charset="windows-1252"
Content-Transfer-Encoding: 7bit

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