Re: Insert speed question - Mailing list pgsql-general

On Wed, Jun 02, 2004 at 08:50:16AM -0600, Josué Maldonado wrote:

> dbmund=# explain analyze
> dbmund-# insert into pk2
> dbmund-# (pkd_stamp,pkd_fecha,
> doctipo,'YYY',-1,-1,-1,-1,prod_no,impues,
> dbmund(# pkd_docto,pkd_es,pkd_qtysold,pkd_qtyinv,
                     ^^^^^^
> dbmund(# pkd_unidad,pkd_price,pkd_costo,pkd_saldo,
> dbmund(# pkd_procode,pkd_custid,pkd_vendedor,pkd_tipprice,
> dbmund(# pkd_totprice,pkd_estanulo,pkd_estmes,pkd_porcomision,
> dbmund(# pkd_rutafk,pkd_provcode,pkd_depto,pkd_pk,
> dbmund(# pkd_totcost,pkd_doctipo2,pkd_doctipo,fk_autorizacion,
> dbmund(# fk_devolucion,pkd_udindexfk,pkd_unidadfk,pkd_prodno,
> dbmund(# pkd_gravada,pkd_fraimp,pkd_imsove,pkd_clmayor,
> dbmund(# pkd_cajanum,pkd_es)
                       ^^^^^^
> dbmund-# select fkardex,facfec,facnum,es,tqtysold,
> dbmund-# invqty,unidad,fprice,fcost,saldo,
> dbmund-# substr(prod_no,8,4),codclie,who_sold,
> dbmund-# pre_tipo,fprice*tqtysold,'U',dtos(fkardex),
> dbmund-# por_comisi,'XXX',substr(prod_no,1,3),
> dbmund-# substr(prod_no,5,2),OID,fcost*tqtysold,
> dbmund-# doctipo,'YYY',-1,-1,-1,-1,prod_no,impues,
> dbmund-# fra_imp,imsove,clmayor,cajanum,es
> dbmund-# from hisventa
> dbmund-# ;
> ERROR:  column "pkd_es" specified more than once
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

So fix your query!  Also what do you expect to happen if you put
constants in the column list?  This certainly looks like a mistake to
me.  Anyway you should really format your query better so you can
understand it and see obvious mistakes.

> dbmund=# explain analyze
> dbmund-# insert into pk2
[...]
>                                                         QUERY PLAN
>
>
---------------------------------------------------------------------------------------------------------------------------
>  Seq Scan on hisventa  (cost=0.00..633607.24 rows=4882546 width=149)
> (actual time=26.647..363517.935 rows=4882546 loops=1)
>  Total runtime: 1042927.167 ms
> (2 rows)

So you are inserting 4 million rows.  This makes a lot of I/O so no
wonder it takes a long time.  I'm not sure if the time is reasonable or
not though; 4M rows/1M ms = 4 rows/ms.  Not that bad.


> >I would ask the question otherway round. What is the level of performance
> >you are looking at for your current workload. By how much this performance
> >is worse than your expectation?
>
> Since I have not tested the server with the production workload yet,
> maybe my perpception of performance is not rigth focused, basically my
> expectation is database must be faster than the current old legacy
> Foxpro system.

If you are going to have big load, you should at least try to code a
simulation with big load, doing random queries (not any query but the
actual queries you'll get from your system -- for example if this is a
web-based app you can try to use Siege or something along those lines).

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"No reniegues de lo que alguna vez creíste"