X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 72C4B53607
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun,  1 May 2005 17:22:06 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 30210-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun,  1 May 2005 20:21:57 +0000 (GMT)
Received: from mail.infodata.ca (mail.infodata.ca [64.18.160.200])
	by svr1.postgresql.org (Postfix) with SMTP id EBB955420F
	for <pgsql-sql@postgresql.org>; Sun,  1 May 2005 17:21:55 -0300 (ADT)
Received: from 127.0.0.1 ([127.0.0.1])
	by mail.infodata.ca (JAMES SMTP Server 2.2.0) with SMTP ID 985
	for <pgsql-sql@postgresql.org>; Sun, 1 May 2005 16:20:04 -0400 (EDT)
Received: from 66.131.75.242 ([66.131.75.242] helo=[192.168.1.103]) by
	ASSP-nospam ; 1 May 05 20:19:57 -0000
Message-ID: <42753A5A.6030004@infodata.ca>
Date: Sun, 01 May 2005 16:21:46 -0400
From: Patrice Drolet <pdrolet@infodata.ca>
User-Agent: Mozilla/5.0 (Macintosh; U; PPC Mac OS X Mach-O; fr-FR;
	rv:1.7.7) Gecko/20050416
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Discordance between the way select is called.
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=
X-Spam-Level: 
X-Archive-Number: 200505/60
X-Sequence-Number: 21373

Hi,

I have a java app that uses hibernate to do queries.

One query on a 6.5 millions records takes about 15 seconds while the 
same one (take from the sql that shows in the consol - I configured 
hibernate to show_sql) takes about 50 ms when done with pgadmin3.

This is a simple select. Here is the log of pgsql:
 
<postgres%patient_record> LOG:  00000: statement: select notevalue0_.id 
as id, notevalue0_.value_note as value2_3_, notevalue0_.actif as 
actif3_, notevalue0_.id_note as id4_3_, notevalue0_.id_field_name as 
id5_3_ from note.note_value notevalue0_ where notevalue0_.id_note=$1 and 
notevalue0_.actif=1
<postgres%patient_record> LOCATION:  pg_parse_query, postgres.c:473
<postgres%patient_record> LOG:  00000: PLANNER STATISTICS
<postgres%patient_record> DETAIL:  ! system usage stats:
    !    0.001171 elapsed 0.000000 user 0.000000 system sec
    !    [0.050000 user 0.010000 sys total]
    !    0/0 [0/0] filesystem blocks in/out
    !    0/0 [0/0] page faults/reclaims, 0 [0] swaps
    !    0 [0] signals rcvd, 0/0 [8/84] messages rcvd/sent
    !    0/0 [7/0] voluntary/involuntary context switches
    ! buffer usage stats:
    !    Shared blocks:          2 read,          0 written, buffer hit 
rate = 95.74%
    !    Local  blocks:          0 read,          0 written, buffer hit 
rate = 0.00%
    !    Direct blocks:          0 read,          0 written
<postgres%patient_record> LOCATION:  ShowUsage, postgres.c:3341
<postgres%patient_record> LOG:  00000: EXECUTOR STATISTICS
<postgres%patient_record> DETAIL:  ! system usage stats:
    !    12.323373 elapsed 10.890000 user 1.140000 system sec
    !    [10.940000 user 1.150000 sys total]
    !    0/0 [0/0] filesystem blocks in/out
    !    0/0 [0/0] page faults/reclaims, 0 [0] swaps
    !    0 [0] signals rcvd, 0/0 [8/87] messages rcvd/sent
    !    0/0 [7/0] voluntary/involuntary context switches
    ! buffer usage stats:
    !    Shared blocks:      44305 read,          0 written, buffer hit 
rate = 0.00%
    !    Local  blocks:          0 read,          0 written, buffer hit 
rate = 0.00%
    !    Direct blocks:          0 read,          0 written

The $1 value is a simple integer and is a foreign key for another table. 
There were 4 rows only meeting this criteria. Can you explain to me the 
executor statistics? Why does it do 44305 read?

This query will be a stopper for us if not faster... :-(  Apart from 
this, we love postgres...

Thanks for any help.

Patrice Drolet
Logiciels INFO-DATA inc.