|
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 POSTGRES #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' S # 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: 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 |
