CODEWITHSUNDEEP
performancepostgresqlquery-optimization
Samson
Samson

Reputation: 2821

postgreSQL optimisation

I am working on a big PostgreSQL project which, unfortunately in production (3 milion records in a table with 90 columns) runs a query (a select/join with 2 conditions) in about 2 minutes.

Supposing there is nothing to optimise on my query, are there any settings I could modify to make it run faster? This is the configuration of the database, and I have no idea what suits my needs: 

version PostgreSQL 8.4.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-44), 32-bit
checkpoint_completion_target    0.9
checkpoint_segments 10
custom_variable_classes pg_stat_statements
effective_cache_size    1GB
lc_collate  fr_FR.UTF-8
lc_ctype    fr_FR.UTF-8
listen_addresses    *
log_autovacuum_min_duration 0
log_line_prefix %t [%p]: [%l-1] user=%u,db=%d
log_min_duration_statement  30s
logging_collector   on
maintenance_work_mem    128MB
max_connections 100
max_stack_depth 2MB
pg_stat_statements.max  1000
pg_stat_statements.save on
pg_stat_statements.track    all
random_page_cost    1.5
server_encoding UTF8
shared_buffers  128MB
TimeZone    Europe/Paris
track_functions pl
wal_buffers 1MB
work_mem    8MB

Query:

SELECT distinct
    ((Table_Commande_Historique.COD_STECIAL
    || ',' || Table_Commande_Historique.COD_MCIAL
    || ',' || Table_Commande_Historique.NUM_REC_CLI
    || ',' || Table_Commande_Historique.NUM_DNT_CLI
    || ',' || Table_Commande_Historique.NUM_DDE)) cle
FROM G1DDE2_DDE Table_Commande_Historique
inner join "K2VER2_VER" ver
  on ( Table_Commande_Historique.NUM_REC_CLI
         = (string_to_array(ver.num_cle,','))[3]::int
    OR Table_Commande_Historique.NUM_DNT_CLI
         = (string_to_array(ver.num_cle,','))[3]::int
    OR ver.num_cle = (Table_Commande_Historique.COD_MCIAL
                     || ',' || Table_Commande_Historique.NUM_REC_CLI)
    OR ver.num_cle = (Table_Commande_Historique.COD_MCIAL
                     || ',' || Table_Commande_Historique.NUM_DNT_CLI) );

Indexes:

CREATE INDEX idx_combo1
  ON g1dde2_dde
  USING btree
  (((cod_mcial || ','::text) || num_rec_cli) );

  CREATE INDEX idx_combo2
  ON g1dde2_dde
  USING btree
  (((cod_mcial || ','::text) || num_dnt_cli) );

  CREATE INDEX idx_dnt
  ON g1dde2_dde
  USING btree
  (num_dnt_cli );

  CREATE INDEX idx_rec
  ON g1dde2_dde
  USING btree
  (num_rec_cli );

  CREATE INDEX idx_k2ver3sb
  ON "K2VER2_VER"
  USING btree
  (num_cle );

EXPLAIN:

"HashAggregate  (cost=197.97..201.77 rows=69 width=29)"
"  ->  Nested Loop  (cost=1.29..197.35 rows=248 width=29)"
"        ->  Seq Scan on "K2VER2_VER" ver  (cost=0.00..2.58 rows=58 width=19)"
"        ->  Bitmap Heap Scan on g1dde2_dde table_commande_historique  (cost=1.29..2.84 rows=5 width=29)"
"              Recheck Cond: ((table_commande_historique.num_rec_cli = ((string_to_array((ver.num_cle)::text, ','::text))[3])::integer) OR (table_commande_historique.num_dnt_cli = ((string_to_array((ver.num_cle)::text, ','::text))[3])::integer) OR ((ver.num_cle)::text = (((table_commande_historique.cod_mcial)::text || ','::text) || (table_commande_historique.num_rec_cli)::text)) OR ((ver.num_cle)::text = (((table_commande_historique.cod_mcial)::text || ','::text) || (table_commande_historique.num_dnt_cli)::text)))"
"              ->  BitmapOr  (cost=1.29..1.29 rows=5 width=0)"
"                    ->  Bitmap Index Scan on idx_rec  (cost=0.00..0.32 rows=2 width=0)"
"                          Index Cond: (table_commande_historique.num_rec_cli = ((string_to_array((ver.num_cle)::text, ','::text))[3])::integer)"
"                    ->  Bitmap Index Scan on idx_dnt  (cost=0.00..0.32 rows=1 width=0)"
"                          Index Cond: (table_commande_historique.num_dnt_cli = ((string_to_array((ver.num_cle)::text, ','::text))[3])::integer)"
"                    ->  Bitmap Index Scan on idx_combo1  (cost=0.00..0.32 rows=1 width=0)"
"                          Index Cond: ((ver.num_cle)::text = (((table_commande_historique.cod_mcial)::text || ','::text) || (table_commande_historique.num_rec_cli)::text))"
"                    ->  Bitmap Index Scan on idx_combo2  (cost=0.00..0.32 rows=1 width=0)"
"                          Index Cond: ((ver.num_cle)::text = (((table_commande_historique.cod_mcial)::text || ','::text) || (table_commande_historique.num_dnt_cli)::text))"

Upvotes: 1

Views: 636

Answers (1)

kgrittn
kgrittn

Reputation: 19501

version PostgreSQL 8.4.4

That's a problem. Please read this and consider a minor release to get fixes for security vulnerabilities and bugs:

http://www.postgresql.org/support/versioning/

These sometimes include bugs which introduce performance problems. To review what fixes you would get, you could look into the 8.4.5 to 8.4.11 notes here:

http://www.postgresql.org/docs/8.4/static/release.html

Some of the RAM-based settings are probably too low, but without knowing how much RAM is on the system and what else is running there, there's no way to suggest specific numbers.

shared_buffers  128MB

The usual advice for a dedicated database server on Linux is to set this to 25% of the total RAM on the system, with a maximum of maybe 8GB, and adjust from there based on benchmarking.

effective_cache_size    1GB

This doesn't allocate any RAM, but allows the planner to cost repeated reads from a file based on an estimate of how likely it is to still be in cache from an earlier read in the same query. I suggest that you add your shared_buffers size to what the OS shows as cached.

work_mem    8MB

This one is tricky. It can really help query performance in a number of ways, but high values tend to push things out of cache, increasing disk access. You also need to consider that each query can allocate this much space several times (for different query steps), so you should normally allow for one allocation of this size per connection allowed. This is one of the reasons it is often beneficial to use a connection pool to funnel a large number of users down to a limited number of actual database connections. If you can afford a larger size, it is very likely to help this query, because it may keep the bitmap index scans from becoming "lossy" and needing to recheck the index conditions.

You don't set cpu_tuple_cost, but I find that the default setting is usually too low to give the best plans overall. Given that your big table has 90 columns, I would suggest boosting this from 0.01 to 0.05.

You don't set effective_io_concurrency, but that might help. I would test with different values. (Of course, when running tests to compare the performance of alternatives, be careful about caching issues.)

maintenance_work_mem    128MB

Depending on how much RAM you have, this may or may not be reasonable. It won't affect your immediate problem, but increasing it may help autovacuum run more efficiently and will help index builds run faster.

These are probably a little low:

checkpoint_segments 10
wal_buffers 1MB

They are not part of your immediate problem, but they could cause extra disk writes at times, so it would probably pay to adjust them. wal_buffers should normally be 32MB unless your machine has very limited RAM. checkpoint_segments is hard to estimate without knowing more, but if you check the logs and statistics and find that checkpoints are happening too frequently, you might want to increase this until checkpoints are happening based on checkpoint_timeout.

Upvotes: 3

Related Questions