> -----Mensaje original----- > De: SZUCS Gábor [mailto:[EMAIL PROTECTED] > Enviado el: jueves, 26 de junio de 2003 7:31 > Para: [EMAIL PROTECTED] > 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