Re: Similar querys, better execution time on worst execution plan

> -----Mensaje original-----
> De: SZUCS Gábor [mailto:surrano(at)mailbox(dot)hu]
> Enviado el: jueves, 26 de junio de 2003 7:31
> Para: pgsql-performance(at)postgresql(dot)org
> 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.