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