Re: Tunning postgresql - Mailing list pgsql-general

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)


Table estprod is:
CREATE TABLE public.estprod
(
   pk_estprod int4 DEFAULT nextval('sqestprod'::text),
   product_fk int4,
   epr_periodo char(6),
   epr_venta numeric(12,4),
   epr_costo numeric(12,4),
   epr_qty numeric(12,4),
   epr_venta2 numeric(12,4),
   epr_costo2 numeric(12,4),
   epr_qty2 numeric(12,4),
   epr_venta3 numeric(12,4),
   epr_costo3 numeric(12,4),
   epr_qty3 numeric(12,4),
   epr_ventax numeric(12,2),
   pro_code char(4),
   xmes varchar(6),
   imes int4
) WITH OIDS;

and it contains 355,513 rows

Stephen Robert Norris wrote:
> On Thu, 2003-11-20 at 02:06, Josué Maldonado wrote:
>
>>Hi Stephen,
>>
>>Stephen Robert Norris wrote:
>>
>>>
>>>Speaking from long experimentation, you're much, much better off making
>>>sure your indices and queries are optimal that messing around with
>>>buffer space. Buffer space tuning might get you a few percent
>>>performance once you pick a reasonable value; query tuning can get you
>>>orders of magnitude.
>>>
>>
>>I tunned my querys and all uses indexes and seems to be fast, but when
>>my client app acccess the pg data it seems a little slow. I installed
>>MSDE (mssql limited version) and copied the same data from pg to MSDE, I
>>was surprised cuz running the same query with the same data and MSDE
>>seems to be a little bit faster, after the changes in postgresql.conf
>>described in the previus message pg perfomance increased a little but
>>still there is no big difference against MSDE, considering hardware, pg
>>is in a real server (dual Xeon 2.4 Ghz, 2G RAM, 3 36G SCSI drives on a
>>RAID5) and the server is not in production, MSDE is in a AMD athlon 1Gh
>>with 256RAM y should expect better performace from pg. I wonder if ODBC
>>could be affect performance so high, my current ODBC driver is 7.03.02.
>>
>>Thanks
>
>
> I have never used ODBC so I don't know how much that will effect
> performance. What are the schemata for the tables, and what does explain
> analyze tell you about the query?
>
>     Stephen
>


--
Josué Maldonado.