From: MirrorX <mirrorx@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: parse - bind take more time than execute
Date: Thu, 29 Dec 2011 08:12:49 -0800 (PST)
Message-ID: <1325175169744-5107985.post@n5.nabble.com> (raw)
In-Reply-To: <4EFB5042020000250004418B@gw.wicourts.gov>
References: <1324983133749-5102940.post@n5.nabble.com>
<4EFB5042020000250004418B@gw.wicourts.gov>
thx for your reply :)
-the timings come from the log
-the table is this ->
\d configurations
Table "public.configurationcontext"
Column | Type | Modifiers
-------------------+------------------------+-----------
id | numeric(18,0) | not null
category | numeric(18,0) |
pr_oid | numeric(18,0) |
var_attrs | character varying(255) |
num_value | numeric(18,0) |
Indexes:
"pk_configurations" PRIMARY KEY, btree (id)
"conf_index" btree (category, pr_oid, num_value)
and one query is this ->
SELECT * FROM configurations WHERE pr_oid=$1 AND num_value=$2
-the table has only 2500 rows
-this messages used to appear a lot after i created a new index for the 2
columns mentioned above in the query, since i thought that the 3-column
index wouldnt be of much help since the first column was not defined in the
query. now i have dropped this extra index and i see much less records in
the log about the bind/parse phase of the query
-the server has 4 cores, 12 GB ram, and fata disks. the settings from the
query are these ->
name |
current_setting
---------------------------------+------------------------------------------------------------------------------------------------------------------
version | PostgreSQL 8.4.7 on
x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat
4.1.2-50), 64-bit
archive_command | cp -i %p /var/lib/pgsql/wals/%f
</dev/null
archive_mode | on
autovacuum_analyze_scale_factor | 0.05
autovacuum_vacuum_scale_factor | 0.1
bgwriter_delay | 50ms
bgwriter_lru_maxpages | 200
bgwriter_lru_multiplier | 4
checkpoint_completion_target | 0.9
checkpoint_segments | 30
checkpoint_timeout | 15min
effective_cache_size | 9GB
lc_collate | en_US.UTF-8
lc_ctype | en_US.UTF-8
listen_addresses | *
log_checkpoints | off
log_directory | pg_log
log_filename | postgresql-%a.log
log_min_duration_statement | 50ms
log_rotation_age | 1d
log_rotation_size | 0
log_truncate_on_rotation | on
logging_collector | on
maintenance_work_mem | 512MB
max_connections | 100
max_prepared_transactions | 20
max_stack_depth | 8MB
port | 5432
server_encoding | UTF8
shared_buffers | 2GB
synchronous_commit | off
temp_buffers | 12800
TimeZone | Europe/Athens
wal_buffers | 16MB
work_mem | 30MB
--
View this message in context: http://postgresql.1045698.n5.nabble.com/parse-bind-take-more-time-than-execute-tp5102940p5107985.htm...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
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: mirrorx@gmail.com
Subject: Re: parse - bind take more time than execute
In-Reply-To: <1325175169744-5107985.post@n5.nabble.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