Query performance on session table - Mailing list pgsql-novice

Hello,

I am having performance issues with a table that holds session_data.
This table is heavily updated and daily vacuumed. Please see the
information below. I was not able to pinpoint the issue so any help
would be appreciated.

Burak


####################
### Table Structure ###
####################

CREATE TABLE session (
session_id char(40) NOT NULL PRIMARY KEY,
session_expires integer NOT NULL,
session_data text
);

##################
### Sample Row ###
##################

1987b8db3ab36c18d0da7f9c2915194092f7bdf7 |      1183066733 |
hkmTZblHHF+cY8g 6Dx/K0ioEc98QdmOKST ocRd P2gUfsTsMeMBV
wGiGbhTJ0CuimDVpv hH8TdWjqMc3rJW7dHJ wjdsrNaqXUpEG+9HvnbKgngG9cqa
p2IxjeTD7k8G/5ZIDrvk3+DSoFu2FB47qvacNmH+hzM U
d1Fn8oKERa1qc+AcuLxLQKdwQUV H8ZE7WXNG etkGq/LSlgIOpTyb44oy5C5evlWSiT1
A2iwCIT8kxrCn5+Avrrg/zLQ muZkBqXd5 vvPcL5 mXEhnu4b96Zy/YpyEnUcV
8coPBw1p0s1i5lwjWyMHYo7H 64HfON8prMizrEoTNyTeMt7jbxo3v
I0Ds+xP9QxY8hpv+4Hc+GN ........ encrypted data continues....

######################################
### EXPLAIN ANALYZE before VACUUM ###
######################################

# explain analyze select * from session;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Seq Scan on session  (cost=0.00..373745.36 rows=736 width=269)
(actual time=32824.964..75997.206 rows=710 loops=1)
 Total runtime: 75998.003 ms
(2 rows)

#####################################
### EXPLAIN ANALYZE after VACUUM ###
#####################################

# explain analyze select * from session;
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Seq Scan on session  (cost=0.00..373753.47 rows=747 width=282)
(actual time=1357.697..60344.110 rows=731 loops=1)
 Total runtime: 60344.971 ms
(2 rows)

60 seconds for 747 rows!

#################
### Other tables ###
#################

Other tables in the database are performing just fine. Please see the
below query plan for reference.

# explain analyze select * from user;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Seq Scan on user  (cost=0.00..2650.49 rows=25849 width=254) (actual
time=22.087..3946.991 rows=25866 loops=1)
 Total runtime: 3971.146 ms
(2 rows)

~4 secs for 25849 rows.

################
### More tests ###
################

# explain analyze select * from session order by session_expires desc limit 1;
                                                            QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=373789.12..373789.12 rows=1 width=282) (actual
time=49065.062..49065.064 rows=1 loops=1)
   ->  Sort  (cost=373789.12..373790.99 rows=747 width=282) (actual
time=49065.058..49065.058 rows=1 loops=1)
         Sort Key: session_expires
         ->  Seq Scan on session  (cost=0.00..373753.47 rows=747
width=282) (actual time=5514.580..49063.397 rows=729 loops=1)
 Total runtime: 49065.152 ms
(5 rows)

# explain analyze select * from session where
session_id='1987b8db3ab36c18d0da7f9c2915194092f7bdf7';
                                                           QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------
 Index Scan using session_pkey on ce_session  (cost=0.00..12.05 rows=1
width=282) (actual time=28.113..28.115 rows=1 loops=1)
   Index Cond: (session_id = '1987b8db3ab36c18d0da7f9c2915194092f7bdf7'::bpchar)
 Total runtime: 28.144 ms
(3 rows)


##################
### Configuration ###
##################

RedHat EL3, PostgreSQL 7.4

# - Memory -

shared_buffers = 30000
sort_mem = 5120
vacuum_mem = 32768

# - Free Space Map -

max_fsm_pages = 20000
max_fsm_relations = 1000

# - Kernel Resource Usage -

max_files_per_process = 1000
preload_libraries = ''

# - Settings -

fsync = true
wal_sync_method = fsync
wal_buffers = 8

# - Checkpoints -

checkpoint_segments = 20
checkpoint_timeout = 300
checkpoint_warning = 30
commit_delay = 0
commit_siblings = 5

# - Planner Cost Constants -

effective_cache_size = 40000
random_page_cost = 4
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.001
cpu_operator_cost = 0.0025