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.
---------------------------(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
