pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDiscordance between the way select is called.
2+ messages / 2 participants
[nested] [flat]
* Discordance between the way select is called.
@ 2005-05-01 20:21 Patrice Drolet <pdrolet@infodata.ca>
0 siblings, 1 reply; 2+ messages in thread
From: Patrice Drolet @ 2005-05-01 20:21 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Discordance between the way select is called.
@ 2005-05-07 16:51 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Patrice Drolet <pdrolet@infodata.ca>
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2005-05-07 16:51 UTC (permalink / raw)
To: Patrice Drolet <pdrolet@infodata.ca>; +Cc: pgsql-sql
> 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_.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;
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?
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-05-07 16:51 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-01 20:21 Discordance between the way select is called. Patrice Drolet <pdrolet@infodata.ca>
2005-05-07 16:51 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
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