--On torsdag, november 01, 2007 09.43.39 -0400 Tom Lane <tgl@xxxxxxxxxxxxx>
wrote:
Palle Girgensohn <girgen@xxxxxxxxxxxx> writes:
When running
select max("when") from login where userid='userid'
it takes a second or two, but when adding "group by userid" the planner
decides on using another plan, and it gets *much* faster. See example
below.
It's only faster for cases where there are few or no rows for the
particular userid ...
Number of tuples per user varies from zero to a couple of thousands.
The planner is using an intermediate estimate of 406 rows. You might be
well advised to increase the statistics target for login.userid --- with
luck that would help it to choose the right plan type for both common
and uncommon userids.
Unfortunately, altering statistics doesn't help. I see no difference when
altering the value from 10 (default) to 100, 1000 or 100000. :-(
Are there any other things I can modify?
OH, btw, maybe something in the postgresql.conf sucks?
max_connections = 100
shared_buffers = 30000 # min 16 or max_connections*2, 8KB
each
temp_buffers = 2500 # min 100, 8KB each
max_prepared_transactions = 100 # can be 0 or more
work_mem = 16384 # min 64, size in KB
maintenance_work_mem = 16384 # min 1024, size in KB
max_stack_depth = 32768 # min 100, size in KB
max_fsm_pages = 500000
max_fsm_relations = 20000
max_files_per_process = 2000
fsync = off
checkpoint_segments = 50 # in logfile segments, min 1, 16MB
each
effective_cache_size = 10000 # typically 8KB each
random_page_cost = 1.8
geqo = on
geqo_threshold = 10
from_collapse_limit = 8
join_collapse_limit = 8 # 1 disables collapsing of explicit
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
