Re: parse - bind take more time than execute

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux