Skip site navigation (1) Skip section navigation (2)

Re: PERSISTANT PREPARE (another point of view)

From: Milan Oparnica <milan(dot)opa(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: PERSISTANT PREPARE (another point of view)
Date: 2008-07-25 17:49:44
Message-ID: g6d3ms$1gt8$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-sql
Jeff Williams wrote:

> You can do this with cursors, but I'm not sure if you still get the
> query caching?
> 

I can do it with cursors, I don't get query cache but that is for now 
not an issue.

Issue is performance.

Although using cursors show better performance over using SET OF user 
defined data types, it still swallows huge amount of memory.

Here are the results of a query (I post the query at the end just for 
sake of complexity example) running on database of 1.6 GB of data and 
around 5 million of records:

			Time	Approx RAM usage	Swaping
PREPARE Statement	5 min.	300 MB			None
SP with SET OF		16 min.	800 MB			Heavy
SP with cursor		9 min.	800 MB			Medium

Environment:

Run on Query Tool of pgAdmin III. It does no data modification.
Number of clients: 1
PostgreSQL version 8.3
Processor P4 2.66 GHZ
RAM memory  1 GB.

There where no other active programs or demanding services running in 
the background.


I presume adding more RAM would increase performance but we usually have 
10 to 20 clients working at a time. I guess that would put some load.

Besides, fetching records from cursor type SP requires transactional 
approach in calling convention (BEGIN....statment...END). At this moment 
I don't know if this can be done in our main report designer tool - 
Business Objects (former Crystal Reports).

Best regards,

Milan Oparnica


Here is the text of SQL statement:

Note: All fields in join are indexed and every where field is also 
indexed except boolean fields.

CREATE FUNCTION aaTestCurs (refcursor, insklid integer, indatdo 
timestamp without time zone) RETURNS refcursor AS $$
BEGIN
     OPEN $1 FOR
     SELECT 10 AS 
Poredak,U.UlID,U.DatDoc,DT.FKomis,DT.FSCObrac,DT.FNivelTot,DT.FNivelParc,
       U.DocTip,SU.SasUlID,SU.ElID,ROUND(CAST(SU.Kol AS NUMERIC),8) AS 
Kol,SU.NCena,ROUND(CAST(SU.TKol AS NUMERIC),8) AS TKol,
       SU.SCena,SU.SklID,0 AS Obradjen,0 AS IzmSC,0 AS ImaIzmena,
       U.Link,SU.FCena,SU.Popust,SU.Marza,SU.MCena,SU.MPorez,SU.KKol,
       SU.PorezU,SUN.NNKol,SUN.NPCena,SUN.NTotal,SUN.NTotPP,SUN.NTotPU,0 
AS NivelEdited,
       SUN.NNKol AS NivelOldKol,0 AS LastTKol,0 AS LastSCena,SK.RacTipSKL,
       U.DTStamp AS Tabela,U.DopSklID,DT.FPrenos,SK.FRemote
       FROM(((defDocTip AS DT INNER JOIN Ulaz AS U ON DT.DTID = 
U.DocTip) INNER JOIN SasUlaz AS SU ON U.UlID = SU.UlID) INNER JOIN 
Skladista AS SK ON U.SklID = SK.SklID) LEFT JOIN SasUlazNivel AS SUN ON 
SU.SasUlID = SUN.SasUlID
       WHERE DT.FSCObrac AND NOT(SK.FSkipSCObr <> false) AND 
NOT(SK.FSkipNivel <> false) AND U.DatDoc <= InDatDo
       UNION ALL
       SELECT 20 AS 
Poredak,U.UlID,U.DatDoc,DT.FKomis,DT.FSCObrac,DT.FNivelTot,DT.FNivelParc,
       U.DocTip,SU.SasUlID,SU.ElID,ROUND(CAST(SU.Kol AS 
NUMERIC),8),SU.NCena,ROUND(CAST(SU.TKol AS NUMERIC),8),
       SU.SCena,SU.SklID,0 AS Obradjen,0 AS IzmSC,0 AS ImaIzmena,U.Link,
 
SU.FCena,SU.Popust,SU.Marza,SU.MCena,SU.MPorez,SU.KKol,SU.PorezU,SUN.NNKol,
       SUN.NPCena,SUN.NTotal,SUN.NTotPP,SUN.NTotPU,0 AS NivelEdited,
       SUN.NNKol AS NivelOldKol,0 AS LastTKol,0 AS 
LastSCena,SK.RacTipSKL,(U.DTStamp -(200000*case when DT.Rank > 50000 
then -1 else 0
end)) AS Tabela,
       U.DopSklID,DT.FPrenos,SK.FRemote
       FROM(((defDocTip AS DT INNER JOIN Ulaz AS U ON   DT.DTID = 
U.DocTip) INNER JOIN SasUlaz AS SU ON U.UlID = SU.UlID) INNER JOIN 
Skladista AS SK ON U.SklID = SK.SklID)  LEFT JOIN SasUlazNivel AS SUN ON 
SU.SasUlID = SUN.SasUlID
       WHERE NOT DT.FSCObrac AND NOT(SK.FSkipSCObr <> false) AND 
NOT(SK.FSkipNivel <> false) AND U.DocTip <> 31 AND U.DatDoc <= InDatDo
       UNION ALL
       SELECT 28 AS Poredak,U.UlID,U.DatDoc,False AS 
FKomis,DT.FSCObrac,DT.FNivelTot,
       DT.FNivelParc,U.DocTip,SU.SasUlID,SU.ElID,ROUND(CAST(SU.Kol AS 
NUMERIC),8),SU.NCena,ROUND(CAST(SU.TKol AS NUMERIC),8),
       SU.SCena,SU.SklID,0 AS Obradjen,0 AS IzmSC,0 AS ImaIzmena,
 
U.Link,SU.FCena,SU.Popust,SU.Marza,SU.MCena,SU.MPorez,SU.KKol,SU.PorezU,
       SUN.NNKol,SU.RCena,SUN.NTotal,SUN.NTotPP,SUN.NTotPU,0 AS 
NivelEdited,
       SUN.NNKol AS NivelOldKol,0 AS LastTKol,0 AS LastSCena,SK.RacTipSKL,
       U.DTStamp AS Tabela,U.DopSklID,DT.FPrenos,SK.FRemote
       FROM(((defDocTip AS DT INNER JOIN Ulaz AS U ON DT.DTID = 
U.DocTip) INNER JOIN SasUlaz AS SU ON U.UlID = SU.UlID) INNER JOIN 
Skladista AS SK ON U.SklID = SK.SklID)  LEFT JOIN SasUlazNivel AS SUN ON 
SU.SasUlID = SUN.SasUlID
       WHERE U.DatDoc <= InDatDo AND NOT(SK.FSkipSCObr <> false) AND 
NOT(SK.FSkipNivel <> false) AND U.DocTip = 31 AND((SK.RacTipSKL = 3 
AND(DT.FMP <> false)) OR(SK.RacTipSKL <> 3 AND(DT.FVP <> false)))
       UNION ALL SELECT 30 AS Poredak,0 AS UlID,O.DatDoc,False AS 
FKomis,False,False,False,2 AS DocTip,
       0 AS SasUlID,SO.ElID,-SUM(SO.Kol) AS Kol,0 AS NCena,0 AS TKol,
       0 AS SCena,SK.SklID,0 AS Obradjen,0 AS IzmSC,0 AS ImaIzmena,0 AS 
Link,
       0 AS FCena,0 AS Popust,0 AS Marza,0 AS MCena,0 AS MPorez,0 AS 
KKol,0 AS PorezU,
       0 AS NNKol,0 AS NPCena,0 AS NTotal,0 AS NTotPP,0 AS NTotPU,0 AS 
NivelEdited,
       0 AS NivelOldKol,0 AS LastTKol,0 AS LastSCena,SK.RacTipSKL,
       200000 AS Tabela,0 AS DopSklID,FALSE AS FPrenos,
       FALSE AS FRemote
       FROM(Otpremnice AS O INNER JOIN SasOtp AS SO ON O.OtpID = 
SO.OtpID)  INNER JOIN Skladista AS SK ON SO.SklID = SK.SklID
       WHERE O.DatDoc <= InDatDo AND SO.ElID < 1000000
       GROUP BY O.DatDoc,SO.ElID,SK.SklID,SK.RacTipSKL
       ORDER BY 3,Tabela,2;
     RETURN $1;
END
$$ LANGUAGE plpgsql;


-- Executing query:
BEGIN;
SELECT aaTestCurs('curs', 1,'31.12.2008');
FETCH ALL FROM curs;
END;
Query result with 1 rows discarded.
Query result with 1564318 rows discarded.

Query returned successfully with no result in 531563 ms.

I guess I didn't populate the cursor.

In response to

Responses

pgsql-sql by date

Next:From: Pavel StehuleDate: 2008-07-25 19:07:18
Subject: Re: PERSISTANT PREPARE (another point of view)
Previous:From: Tom LaneDate: 2008-07-25 17:20:43
Subject: Re: Query prepared plan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group