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
