From: | "Fernando Papa" <fpapa(at)claxson(dot)com> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Cc: | "Programador4" <programador4(at)claxson(dot)com>, Nicolás Peralta <nperalta(at)claxson(dot)com>, "Alejandro Maierowicz" <amaierowicz(at)claxson(dot)com> |
Subject: | Re: Similar querys, better execution time on worst execution plan |
Date: | 2003-06-26 13:33:38 |
Message-ID: | F1DC5B511E2D1C499E5E20FC6D74160D03642249@exch2000.buehuergo.corp.claxson.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> -----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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-06-26 14:08:05 | Re: problem with pg_statistics |
Previous Message | Andre Schubert | 2003-06-26 13:15:15 | problem with pg_statistics |