Thread: Discordance between the way select is called.

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/involuntarycontext 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.

> 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.

We could answer better with explain output.  Something like:

prepare foo(int) as select notevalue0_.idas id, notevalue0_.value_note as value2_3_, notevalue0_.actif asactif3_,
notevalue0_.id_noteas id4_3_, notevalue0_.id_field_name asid5_3_ from note.note_value notevalue0_ where
notevalue0_.id_note=$1andnotevalue0_.actif=1;
 

explain analyze execute foo(<put a representative value here>);


-
As an initial guess, I'd wonder if it's guessing that for an arbitrary
id_note value that it should do a sequence scan.  Are there a large number
of very common id_note values that might be throwing it off?