DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject
	:content-type:content-transfer-encoding;
	b=jU1stSby/2Vyc8H8QHFTM8vjjflMTDzpIqy/Zk5XavKh5/GseYlkZQy770VGQ9yJtM
	yUyt/cNuZUKr/Wl6kJlb7vaod9yO6TI4aC11KA84KU+6PZTFZ9hLRscN3tKcE9VOlrYg
	q259m/2FaRRMrOiFqeJX/dC0yWYF4b/b4R7mE=
Message-ID: <486CA74B.2000901@gmail.com>
Date: Thu, 03 Jul 2008 12:17:47 +0200
From: Mokhtari Amine <amine.mokhtari@gmail.com>
User-Agent: Thunderbird 2.0.0.14 (X11/20080421)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: cursor/Fetch mechanisms under postgreSQL
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I have juste some questions about cursor/Fetch mechanisms under postgreSQL.

Do it use only scan table approach when one fetch (next) is executed or 
it takes into account index when this lasts one is able to increase the 
performance.

 
I did some experimentation wherein I calculated real wall clock time for 
a same cursor/Fetch C1 with and without index over one field.
 
I noticed that the time response is the same !!!!

I analyzed a same query (Q1) encapsulated and not encapsulated in a 
cursor (c1) and i had the following plan:

- for a Query all alone : explain analyze SELECT id_ville, taux_crim, 
temp_ann FROM ville WHERE (pop between 50000 AND 100000) AND (taux_crim 
between 13 AND 16);
I have the following plan:
------------------------------------------------------------------------------------------------------------------------
"Bitmap Heap Scan on ville  (cost=253.82..1551.48 rows=915 width=12) 
(actual time=12.863..24.354 rows=1309 loops=1)"
"  Recheck Cond: ((taux_crim >= 13) AND (taux_crim <= 16))"
"  Filter: ((pop >= 50000) AND (pop <= 100000))"
"  ->  Bitmap Index Scan on taux_crim_idx  (cost=0.00..253.59 rows=13333 
width=0) (actual time=12.482..12.482 rows=13381 loops=1)"
"        Index Cond: ((taux_crim >= 13) AND (taux_crim <= 16))"
"Total runtime: 27.464 ms"
------------------------------------------------------------------------------------------------------------------------

- for a same query encapsulated in a curseur: explain analyze declare c1 
cursor for SELECT id_ville, taux_crim, temp_ann FROM ville WHERE (pop 
between 50000 AND 100000) AND (taux_crim between 13 AND 16);

i have another plan:
-----------------------------------------------------------------------------------------------------------------------
"Seq Scan on ville  (cost=0.00..3031.00 rows=915 width=12)"
"  Filter: ((pop >= 50000) AND (pop <= 100000) AND (taux_crim >= 13) AND 
(taux_crim <= 16))"
-----------------------------------------------------------------------------------------------------------------------


If index can increase the performance, why this last one is not used by 
a cursor ??? why ?
who to calculate the threshold where it is able to use index ?

 
REMARK: this experimentation has been done with a little program wrote 
in ECPG.

-- 
Mr. Amine Mokhtari,
Phd Student,
IRISA Lab.                     
Addr: Irisa / Enssat Technopole Anticipa, 6, rue de Kerampont, BP
	80518-22305     Lannion Cedex

Email : amine.mokhtari@irisa.fr
        amine.mokhtari@enssat.fr
        amine.mohktari@gmail.fr

Fix: +33 (0)2 96 46 91 00
Mob: +33 (0)6 70 87 58 72
Fax: +33 (0)2 96 37 01 99