Hi All,
I'm really like this list. Thank you for all the invaluable
information! May I ask a question?
I've got a table with about 8 million rows and growing. I must run
reports daily off this table, and another smaller one. Typical query -
joins, groupings and aggregates included. This certain report takes
about 10 minutes on average and is getting longer. I've created all the
indices I think are necessary.
Any advice on how I can get this puppy to go faster? Hardware changes
are not an option at this point, so I'm hoping there is something else I
can poke at. Anyone?
Todd
POSTGRESQL CONF:
#log_connections = on
#fsync = off
#max_connections = 64
# Any option can also be given as a command line switch to the
# postmaster, e.g., 'postmaster -c log_connections=on'. Some options
# can be set at run-time with the 'SET' SQL command.
# See /usr/share/doc/postgresql/README.postgresql.conf.gz for a full
list
# of the allowable options
debug_level = 0
log_connections = on
log_pid = on
log_timestamp = on
syslog = 0
# if syslog is 0, turn silent_mode off!
silent_mode = off
syslog_facility = LOCAL0
trace_notify = off
max_connections = 128
# shared_buffers must be at least twice max_connections, and not less
than 16
shared_buffers = 256
# TCP/IP access is allowed by default, but the default access given in
# pg_hba.conf will permit it only from localhost, not other machines.
tcpip_socket = 1
EXPLAIN ANALYZE for the query:
prod=# explain analyze SELECT t.tgpid, t.directoryname, t.templateid,
count(*) AS requested FROM (spk_tgp t JOIN spk_tgplog l ON ((t.tgpid =
l.tgpid))) GROUP BY t.tgpid, t.directoryname, t.templateid;
NOTICE: QUERY PLAN:
Aggregate (cost=2740451.66..2820969.41 rows=805178 width=48) (actual
time=460577.85..528968.17 rows=1875 loops=1)
-> Group (cost=2740451.66..2800839.97 rows=8051775 width=48) (actual
time=460577.57..516992.19 rows=8117748 loops=1)
-> Sort (cost=2740451.66..2740451.66 rows=8051775 width=48)
(actual time=460577.55..474657.59 rows=8117748 loops=1)
-> Hash Join (cost=128.26..409517.83 rows=8051775
width=48) (actual time=11.45..85332.88 rows=8117748 loops=1)
-> Seq Scan on spk_tgplog l (cost=0.00..187965.75
rows=8051775 width=8) (actual time=0.03..28926.67 rows=8125690 loops=1)
-> Hash (cost=123.41..123.41 rows=1941 width=40)
(actual time=11.28..11.28 rows=0 loops=1)
-> Seq Scan on spk_tgp t (cost=0.00..123.41
rows=1941 width=40) (actual time=0.06..7.60 rows=1880 loops=1)
Total runtime: 529542.66 msec