Re: Tunning postgresql

On Thu, 2003-11-20 at 10:20, Josué Maldonado wrote:
> Stephen,
>
> This is the query code:
> SELECT
> (cmes(substr(epr_periodo,5,2))||'-'||substr(epr_periodo,3,2))::char(6)
> AS hmes,
>     epr_periodo,
>     coalesce(epr_venta,0)::numeric(12,4) as epr_venta,
>     coalesce(epr_costo,0)::numeric(12,4) as epr_costo,
>     coalesce(epr_qty,0)::numeric(12,4) as epr_qty,
>     coalesce(epr_qty2,0)::numeric(12,4) as epr_qty2,
>     coalesce(epr_qty3,0)::numeric(12,4) as epr_qty3,
>     case when epr_qty=0 then 0 else
>         coalesce((epr_costo/epr_qty),0)::numeric(12,4)
>     end as cost_prom,
>     case when epr_qty=0 then 0 else
>         coalesce((epr_venta/epr_qty),0)::numeric(12,4)
>     end as prec_prom,
>     case when epr_costo=0 then 0 else
>      coalesce((((epr_venta/epr_costo)*100)-100),0)::numeric(12,4)
>     end as margen
> FROM  estprod  WHERE  pro_code = '1017' and epr_periodo >= '200211' and
> epr_periodo <= '200311'
>
> This is the explain:
> Index Scan using idx_estx on estprod  (cost=0.00..38.29 rows=9 width=67)
> (actual time=0.52..1.64 rows=13 loops=1)
>     Index Cond: ((pro_code = '1017'::bpchar) AND (epr_periodo >=
> '200211'::bpchar) AND (epr_periodo <= '200311'::bpchar))
>   Total runtime: 1.70 msec
> (3 rows)
>

This looks pretty good - 1.7ms is very quick for a query. If you're
seeing performance problems, it must be from the ODBC layer.

    Stephen