Hi,
I am running PostgreSQL 7.3 on a Linux box (RHEL 2.1 - Xeon 2.8GHz
with 1GB of RAM) and seeing very high CPU usage (normally over 90%)
when I am running the following queries, and the queries take a long
time to return; over an hour!
CREATE TEMPORARY TABLE fttemp1600384653 AS SELECT * FROM ftoneway LIMIT 0;
INSERT INTO fttemp1600384653 SELECT epId, TO_TIMESTAMP(start,
'YYYY-MM-DD HH24:00:00.0')::timestamp AS start, 60 AS consolidation,
SUM(cnt) AS cnt FROM ftone WHERE consolidation = 0 AND start <
TO_TIMESTAMP('2006-06-27 18:43:27.391103+1000', 'YYYY-MM-DD
HH24:00:00.0')::timestamp;
DELETE FROM ONLY ftone WHERE ftoneway.epId= fttemp1600384653.epId;
The only changes I've made to the default postgresql.comf file are listed below:
LC_MESSAGES = 'en_US'
LC_MONETARY = 'en_US'
LC_NUMERIC = 'en_US'
LC_TIME = 'en_US'
tcpip_socket = true
max_connections = 20
effective_cache_size = 32768
wal_buffers = 128
fsync = false
shared_buffers = 3000
max_fsm_relations = 10000
max_fsm_pages = 100000
The tables are around a million rows but when when I run against
tables of a few hundred thousand rows it still takes tens of minutes
with high CPU. My database does have a lot of tables (can be several
thousand), can that cause performance issues?
Thanks,
Neil
