long running query running too long - Mailing list pgsql-performance

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

#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

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