Re: PostgreSQL Query Speed Issues - Mailing list pgsql-novice

Tom Lisjac <netdxr@gmail.com> wrote:

> version 9.2.1 running on Centos6/64

There are performance problems in the 9.2 branch which are fixed in
9.2.3.  If you care about performance, or for that matter running
with known bugs fixed, upgrade.

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

> The database is running in a Xen VM with 12GB of ram, 4 virtual
> CPU's and fast, dedicated physical disks rather then shared
> network storage.  The problem queries take one core to saturation
> and keep it there with very little disk I/O.

> max_connections = 200

You may want to consider connection pooling:

http://wiki.postgresql.org/wiki/Number_Of_Database_Connections

> shared_buffers = 2GB

The usual advice is to start at 25% of machine RAM, up to 8GB, and
check performance with incremental updates from there.  That would
suggest a 4GB starting point.

> effective_cache_size = 1024MB

This should normally be around 75% of machine RAM, so 9GB.  This
does not actually allocate any RAM, but is used to estimate how
much of the indexes may be in RAM on repeated access.  A larger
number allows more index usage.

In addition, with 12GB and apparently not more than 2 million rows
per table, you seem very likely to have the active portion of your
database fully cached.  So these settings are likely to help:

seq_page_cost = 0.1
random_page_cost = 0.1
cpu_tuple_cost = 0.03

I normally don't set work_mem above RAM * 0.25 / max_connections,
so I'd say 10MB to 15MB would probably be good with your
max_connections setting.  If you can lower that with a connection
pool you might want to go to 20MB or 40MB.

You probably want to boost maintenance_work_mem to something like
512MB.

If you haven't already done so, run VACUUM ANALYZE at the database
level.  If most of your data was loaded at about the same time, run
VACUUM FREEZE ANALYZE instead, to prevent a read-and-rewrite pass
of your entire database at peak OLTP load.  Vacuum and analyze are
routine maintenance that are necessary for the database to perform
well.  Autovacuum can often handle everything for you, but if you
have an "off-hours" period when load is lower it is often a good
idea to run a database VACUUM ANALYZE on a daily or weekly basis to
shift maintenance load to time when it has the least impact.  Never
leave autovacuum disabled beyond a short maintenance or load
window.

Regarding your specific problem...

You claim this is how contact_address_map is defined:

create table contact_address_map (
    contact id int8 not null,
    address_id int8 not null,
    unique (address_id)
);

... but your query is looking for rows in that table where
address_id is null, and finding them.  Those can't both be true.

Your plan shows an index scan with no index conditions to pass
every row in a table, which is much slower than a seqscan.  Did you
turn off enable_seqscan and fail to mention that?  Doing so will
definitely result in sub-optimal performance on queries like the
one shown, where every row in one of the tables must be read.

When you don't provide accurate information, any advice you get may
be off-target.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company