> -----Mensaje original-----
> De: SZUCS Gábor [mailto:[EMAIL PROTECTED] 
> Enviado el: jueves, 26 de junio de 2003 7:31
> Asunto: Re: [PERFORM] Similar querys, better execution time 
> on worst execution plan
> Fernando,
> 1. Try EXPLAIN ANALYZE. Cost alone isn't an absolute measure. 
> I think it's only to see which parts of the query are 
> expected to be slowest. However, EXP ANA will give you exact 
> times in msec (which effectively means it executes the query).

Ok, yes, I did only explay because I run several times the query and get avg. run 
time. but it's true, it's better to do EXP ANA.
> 2. I think calling upper() for each row costs more than 
> direct comparison, but not sure

It's the only answer than I can found... maybe do a lot of uppers and then compare 
will be too much than compare with 2 conditions...
> 3. Notice that there are seq scans with filter conditions like
>   "id_instalacion = 2::numeric"
>   Do you have indices on id_instalacion, which seems to be a 
> numeric field? if so, try casting the constant expressions in 
> the query to numeric so that postgresql may find the index. 
> If you don't have such indices, it may be worth to create 
> them. (I guess you only have it on the table aliased with c, 
> since it does an index scan there.

Yes, we have index on id_instalacion, but now we have only one instalation, so the 
content of these field, in the 99% of the rows, it's 2. I think in this case it's ok 
to choose seq scan.
> 4. another guess may be indices on (id_instalacion, activo), 
> or, if activo has few possible values (for example, it may be 
> only one of three letters, say, 'S', 'A' or 'K'), partial 
> indices like:
> CREATE INDEX cont_sbc_id_ins_S ON cont_sbc (id_instalacion)
>     WHERE activo in ('S', 's');
> CREATE INDEX cont_sbc_id_ins_A ON cont_sbc (id_instalacion)
>     WHERE activo in ('A', 'a');
> CREATE INDEX cont_sbc_id_ins_K ON cont_sbc (id_instalacion)
>     WHERE activo in ('K', 'k');

I need to recheck about the "quality" of "active" field. Really I don't know if I 
found a lot of 'S', a lot of 'N', maybe we will have 50%/50% of 'S' or 'N'. This will 
be important to define index.

Thanks for your answer.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

Reply via email to