From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Ruben Rubio <ruben@rentalia.com>
Cc: Gregory S. Williamson <gsw@globexplorer.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimization of this SQL sentence
Date: Tue, 17 Oct 2006 11:00:18 +0100
Message-ID: <4534A9B2.8060401@enterprisedb.com> (raw)
In-Reply-To: <4534A35E.9030909@rentalia.com>
References: <45349CDF.3090002@rentalia.com>
<71E37EF6B7DCC1499CEA0316A256832802B3E738@loki.wc.globexplorer.net>
<4534A35E.9030909@rentalia.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: heikki@enterprisedb.com, ruben@rentalia.com, gsw@globexplorer.com
Subject: Re: Optimization of this SQL sentence
In-Reply-To: <4534A9B2.8060401@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox