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

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

Hi to everyone,

First of all I have to say that I now the database is not ok. There was
a people before me that didn't do the thinks right. I would like to
normalize the database, but it takes too much time (there is is hundred
of SQLs to change and there is not enough technical resources). Also,
datacolumns in some places has same names, but the data that is stores
has different usages.

Thanks everyone for all hints, I ll try to do my best performing the
database structure.

By other hand, I was able to create the corrects index and with this

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

it is really fast.

Thanks to everybody.

Regards,
Ruben Rubio



Heikki Linnakangas escribió:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>
>

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

iD8DBQFFNK+jIo1XmbAXRboRAu6cAKCMUWHjcAYwN4DhVl1tSjMirgRAawCgvk8c
gSB/4p1ZBOrDEwU9EW/yxw8=
=yFoD
-----END PGP SIGNATURE-----