performance problem

nightowl

I have PostgresSql running and it is terribly slow for certain queries (for a lot actually). I would like to find a site about finetuning Postgres for better performance (read the article here on the site) but also about indexing and other measures to improve performance.

Let me give you an example :

pm=> select count(*) from portfolio;

count

3067
(1 row)

pm=> select count(*) from users;

count

954
(1 row)

pm=> select count(*) from users where user_id not in (select user_id from portfolio);

count

339
(1 row)

The 3rd query took about 15 (!) seconds to resolve. Working at the terminal right in the local database... Not that good, right ?

I have a not_unique, not_primary key on user_id in portfolio and a key on user_id in users. What else can I change ? (users has about 50 fields, while portfolio only has 6).

nightowl

pm=> explain select count(*) from users where user_id not in (select user_id from portfolio);

NOTICE: QUERY PLAN:

Aggregate (cost=186.57..186.57 rows=1 width=0)
-> Seq Scan on users (cost=0.00..186.56 rows=5 width=0)
SubPlan
-> Seq Scan on portfolio (cost=0.00..72.20 rows=3020 width=4)

EXPLAIN

nightowl

You might also want to know that the database is Postgres 7.1 on Linux Debian.

There are 5 databases on this system (not heavily loaded).

The system is a Pentium 4 with (according to 'top') 384 Mb Rimm-Ram. That should be enough, no ?

smarlowe

It looks like your postgresql installation thinks seq scans are faster than using the index, which is almost never true.

Edit your postgresql.conf file (located in the $PGDATA directory of the postgres super user)

to change the following lines to these seettings:

cpu_tuple_cost = 0.0001
cpu_index_tuple_cost = 0.0000000001
cpu_operator_cost = 0.25

if you can, change the sort_mem variable to something higher, like 512 or something like that, as well as making shared_buffers bigger.

To make them very big you have to change some kernel params like file-max and shmmax and such. But for smaller sites without real heavy parallel load, you can get good numbers from postgresql without needing to tweak your kernel.

nightowl

Thx for the information

After having a look in this file, the only two lines which are not commented out are

Max_connections = 64
shared_buffers = 150

I do have much lines which are commented out including the following ones... should I all uncomment them ?

Optimizer Parameters

#
#enable_seqscan = true
#enable_indexscan = true
#enable_tidscan = true
#enable_sort = true
#enable_nestloop = true
#enable_mergejoin = true
#enable_hashjoin = true

#ksqo = false
#geqo = true

#effective_cache_size = 1000 # default in 8k pages
#random_page_cost = 4
#cpu_tuple_cost = 0.01
#cpu_index_tuple_cost = 0.001
#cpu_operator_cost = 0.0025
#geqo_selection_bias = 2.0 # range 1.5-2.0

nightowl

Current situation (changed the config-file) and stopped and started the server (is this necessary ?)

Config file :

Max_connections = 64
shared_buffers = 150
enable_seqscan = true
enable_indexscan = true
enable_tidscan = true
enable_sort = true
enable_nestloop = true
enable_mergejoin = true
enable_hashjoin = true
effective_cache_size = 1000 # default in 8k pages
random_page_cost = 4
cpu_tuple_cost = 0.0001
cpu_index_tuple_cost = 0.0000000001
cpu_operator_cost = 0.25
geqo_selection_bias = 2.0 # range 1.5-2.0
sort_mem = 512

That is all it has in it (other lines are commented out)

The same query (which is I guess a good benchmark?) still takes 13 to 14 seconds to run... Much too long ! It keeps going for the seq scan too ... (dubble checked : unique, not primary index on user_id in users and a not unique, not primary index on user_id in portfolio according to phpPgAdmin).

Maybe I would need to do some "recommended" reading, since I have only installed this database for the first time now and I am running it to lead a game with over 900 participants ... Does anybody know a good site and/or book ? However, a fast and reliable solution to improve the speed of the database would be appreciated even more!

pm=> explain select count(*) from users where user_id not in (select user_id from portfolio);
NOTICE: QUERY PLAN:

Aggregate (cost=114.26..114.26 rows=1 width=0)
-> Seq Scan on users (cost=0.00..113.01 rows=5 width=0)
SubPlan
-> Seq Scan on portfolio (cost=0.00..42.30 rows=3020 width=4)

EXPLAIN
pm=>
pm=> select count(*) from users where user_id not in (select user_id from portfolio);

count

339
(1 row)

smarlowe

change enable_seqscan = true to false and see if it gets better. The dbms can still do seqscans, but the planner will only use them when there are no other methods available.

and yes, stopping and restarting postgresql is necessary. How did you stop and restart it? I recommend using pg_ctl stop;pg_ctl start.

smarlowe

You might wanna make an index by hand on that column just to make sure it has one it can use.

It should be using the index of the autoincrementing id field, but isn't for some reason.