From: Mokhtari Amine <amine.mokhtari@gmail.com>
To: pgsql-performance@postgresql.org
Subject: cursor/Fetch mechanisms under postgreSQL
Date: Thu, 03 Jul 2008 12:17:47 +0200
Message-ID: <486CA74B.2000901@gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: amine.mokhtari@gmail.com
Subject: Re: cursor/Fetch mechanisms under postgreSQL
In-Reply-To: <486CA74B.2000901@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox