X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id C2C38D1DAB1 for ; Tue, 17 Feb 2004 17:07:39 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 45921-07 for ; Tue, 17 Feb 2004 13:07:30 -0400 (AST) Received: from sccrmhc13.comcast.net (sccrmhc13.comcast.net [204.127.202.64]) by svr1.postgresql.org (Postfix) with ESMTP id 85C2FD1DA83 for ; Tue, 17 Feb 2004 13:07:28 -0400 (AST) Received: from juarez (c-67-164-12-23.client.comcast.net[67.164.12.23]) by comcast.net (sccrmhc13) with SMTP id <20040217170655016003a7gne> (Authid: the_pongo@comcast.net); Tue, 17 Feb 2004 17:07:05 +0000 From: "Todd Fulton" To: Subject: long running query running too long Date: Tue, 17 Feb 2004 09:06:48 -0800 Message-ID: <001301c3f578$7649bb10$326aa8c0@juarez> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0014_01C3F535.68267B10" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/188 X-Sequence-Number: 5712 This is a multi-part message in MIME format. ------=_NextPart_000_0014_01C3F535.68267B10 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_0014_01C3F535.68267B10 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

Hi All,

 

I’m really like this list.  Thank you for all the invaluable information!  May I ask a ques= tion?

 

I’ve got a table with about 8 million rows and growing.  I must run reports d= aily off this table, and another smaller one.  Typical query – joins, groupings and aggregates included.  This cer= tain 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

 

 

 

= POSTGRESQ= L = CONF:

 

#log_connections =3D on=

#fsync =3D off

#max_connections =3D 64=

 

# Any option can also be give= n as a command line switch to the

# postmaster, e.g., 'postmast= er -c log_connections=3Don'. Some options=

# can be set at run-time with the 'SET' S<= st1:PersonName>Q= L = command.

 

# See /usr/share/doc/postgresql/REA= DME.postgresql.conf.gz for a full list

# of the allowable options

 

debug_level = =3D 0

log_connections<= /span> = =3D on

log_pid = =3D on

log_timestamp = =3D on

syslog = =3D 0

# if sys= log is 0, turn silent_mode off!

silent_mode = =3D off

syslog_facility<= /span> = =3D LOCAL0

trace_notify = =3D off

max_connections<= /span> = =3D 128

# shared_buffer= s must be at least twice max_connections, and not= less than 16

shared_buffers = =3D 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 = =3D 1

 

 

= EXPLAIN ANALYZE for the query:=

 

prod= =3D# explain analyze SELECT t.tgpid, t.= directoryname, t.templateid, count(*) AS requested FROM (spk_tgp t JOIN spk_tgplog= l ON ((t.tgpid =3D l.tgpid))) GR= OUP BY t.tgpid, t.directoryname,= t.templateid;

NOTICE:  Q= UE= RY PLAN:

 

Aggregate  (cost=3D2740= 451.66..2820969.41 rows=3D805178 width=3D48) (actual time=3D460577.85..528968.17 rows=3D1875 l= oops=3D1)

  ->  Group  (cost=3D2740451.66..2800839.97 rows=3D8051775 width=3D48) (actual time=3D460577.57..516992.19 rows=3D8117748 loops=3D1)

    = ;    ->  Sort  (cost=3D2740451.66..2740451.66 row= s=3D8051775 width=3D48) (actual time=3D460577.55..474657.59 rows=3D8117748 loops=3D1)

    = ;          ->  Hash Join  (cost=3D128.26..409517.83 rows=3D8= 051775 width=3D48) (actual time=3D11.45..85332.88 rows=3D8117748 loops=3D1)

    = ;            &n= bsp;   ->  Seq Scan on spk_tgplog l  (cost=3D0.00..187965.75 rows=3D8051775 width=3D8) (actual time=3D0.0= 3..28926.67 rows=3D8125690 loops=3D1)

    = ;            &n= bsp;   ->  Hash  (cost=3D123.41..123.41 rows=3D1941= width=3D40) (actual time=3D11.28..11.28 rows=3D0 loops=3D1)

    = ;            &n= bsp;         ->  Seq Scan on spk_tgp t  (cost=3D0.00..123.41 rows=3D1941 w= idth=3D40) (actual time=3D0.06..7.60 rows=3D1880 loops=3D1)

Total runtime: 529542.66 msec

 

 

 

 

 

------=_NextPart_000_0014_01C3F535.68267B10--