pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
long running query running too long
5+ messages / 3 participants
[nested] [flat]

* long running query running too long
@ 2004-02-17 17:06  Todd Fulton <pongo@jah.net>
  0 siblings, 2 replies; 5+ messages in thread

From: Todd Fulton @ 2004-02-17 17:06 UTC (permalink / raw)
  To: 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
 
 
 
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
 
 
 
 
 

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: long running query running too long
@ 2004-02-17 20:05  PC Drew <drewpc@ibsncentral.com>
  parent: Todd Fulton <pongo@jah.net>
  1 sibling, 1 reply; 5+ messages in thread

From: PC Drew @ 2004-02-17 20:05 UTC (permalink / raw)
  To: Todd Fulton <pongo@jah.net>; +Cc: pgsql-performance


On Feb 17, 2004, at 10:06 AM, Todd Fulton wrote:
>
>
> 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.
>
>

What indexes have you created?  The query is not using any indexes, so 
there might be a problem there.  Can you disable seqscans temporarily 
to test this?

>
> 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;


Can you please send the results of the following commands:

psql=# \d spk_tgp

and

psql=# \d spk_tgplog


You might also want to try using a sub-query instead of a join.  I'm 
assuming that the spk_tgplog table has a lot of rows and spk_tgp has 
very few rows.  It might make sense to try something like this:

EXPLAIN ANALYZE
SELECT t.tgpid, t.directoryname, t.templateid, r.requested
FROM (SELECT tgpid, count(*) AS requested FROM spk_tgplog GROUP BY 
tgpid) r, spk_tgp t
WHERE r.tgpid = t.tgpid;

--
PC Drew




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: long running query running too long
@ 2004-02-17 20:41  Todd Fulton <pongo@jah.net>
  parent: PC Drew <drewpc@ibsncentral.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Todd Fulton @ 2004-02-17 20:41 UTC (permalink / raw)
  To: 'PC Drew' <drewpc@ibsncentral.com>; +Cc: pgsql-performance

Hey!  I think I have appropriate indexes, but might now.  You're
absolutely right on my join -- spk_tgplog has the 8.5 million rows,
spk_tgp around 2400.  I'll try the sub-select.  Here is the output you
asked for:

spank_prod=# \d spk_tgp;
                                             Table "spk_tgp"
     Column     |           Type           |
Modifiers
----------------+--------------------------+----------------------------
---------------------------------
 tgpid          | bigint                   | not null
 directoryname  | character varying(64)    | not null
 directoryurl   | character varying(1028)  | not null
 submiturl      | character varying(1028)  |
 submitdate     | date                     |
 acceptdate     | date                     |
 templateid     | character varying(64)    | not null
 reciprocalcode | character varying(2056)  |
 notes          | character varying(2056)  |
 createdate     | timestamp with time zone | not null default
('now'::text)::timestamp(6) with time zone
 modifydate     | timestamp with time zone | not null default
('now'::text)::timestamp(6) with time zone
 requested      | integer                  |
 hostid         | integer                  | default 1
Indexes: idx_spk_tgp_tgpid
Primary key: pk_spk_tgp

spank_prod=# \d idx_spk_tgp_tgpid
       Index "idx_spk_tgp_tgpid"
    Column     |         Type
---------------+-----------------------
 tgpid         | bigint
 directoryname | character varying(64)
btree

spank_prod=# \d spk_tgplog;
                                           Table "spk_tgplog"
    Column     |           Type           |
Modifiers
---------------+--------------------------+-----------------------------
--------------------------------
 remoteaddress | character varying(32)    | not null
 tgpid         | bigint                   | not null
 referer       | character varying(256)   |
 createdate    | timestamp with time zone | not null default
('now'::text)::timestamp(6) with time zone
Indexes: idx_spk_tgplog_createdate,
         idx_spk_tgplog_tgpid

spank_prod=# \d idx_spk_tgplog_createdate
   Index "idx_spk_tgplog_createdate"
   Column   |           Type
------------+--------------------------
 createdate | timestamp with time zone
btree

spank_prod=# \d idx_spk_tgplog_tgpid
Index "idx_spk_tgplog_tgpid"
 Column |  Type
--------+--------
 tgpid  | bigint
btree



Todd



-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of PC Drew
Sent: Tuesday, February 17, 2004 12:05 PM
To: Todd Fulton
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] long running query running too long


On Feb 17, 2004, at 10:06 AM, Todd Fulton wrote:
>
>
> 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.
>
>

What indexes have you created?  The query is not using any indexes, so 
there might be a problem there.  Can you disable seqscans temporarily 
to test this?

>
> 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;


Can you please send the results of the following commands:

psql=# \d spk_tgp

and

psql=# \d spk_tgplog


You might also want to try using a sub-query instead of a join.  I'm 
assuming that the spk_tgplog table has a lot of rows and spk_tgp has 
very few rows.  It might make sense to try something like this:

EXPLAIN ANALYZE
SELECT t.tgpid, t.directoryname, t.templateid, r.requested
FROM (SELECT tgpid, count(*) AS requested FROM spk_tgplog GROUP BY 
tgpid) r, spk_tgp t
WHERE r.tgpid = t.tgpid;

--
PC Drew


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: long running query running too long
@ 2004-02-17 23:17  PC Drew <drewpc@ibsncentral.com>
  parent: Todd Fulton <pongo@jah.net>
  0 siblings, 0 replies; 5+ messages in thread

From: PC Drew @ 2004-02-17 23:17 UTC (permalink / raw)
  To: Todd Fulton <pongo@jah.net>; +Cc: pgsql-performance


On Feb 17, 2004, at 1:41 PM, Todd Fulton wrote:
>
> spank_prod=# \d idx_spk_tgp_tgpid
>        Index "idx_spk_tgp_tgpid"
>     Column     |         Type
> ---------------+-----------------------
>  tgpid         | bigint
>  directoryname | character varying(64)
> btree
>

A couple of things to note:

1.  What version of PostgreSQL are you running?  I'm currently running 
7.3.4 and my output of \d on a table shows more index information than 
yours does.  If you're running anything earlier than 7.3, I'd 
definitely recommend that you upgrade.

2.  Why are you using a multicolumn index in this case?  You might want 
to read the page in the documentation that discusses multi-column 
indexes specifically.

http://www.postgresql.org/docs/7.4/interactive/indexes-multicolumn.html

In any case, it might even be the case that the index isn't being used 
at all.  Does anyone know if indexes are used in a case like this:

> spk_tgp t JOIN spk_tgplog l ON (t.tgpid = l.tgpid)

My hunch is that it's not used.  My understanding is that an index acts 
more as a shortcut so the database doesn't have to go through the 
entire table to look for specific values.  When joining two tables, 
however, you inherently have to go through the entire table.  If anyone 
can clarify this, that'd be great.

--
PC Drew




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: long running query running too long
@ 2004-02-17 23:55  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Todd Fulton <pongo@jah.net>
  1 sibling, 0 replies; 5+ messages in thread

From: Tom Lane @ 2004-02-17 23:55 UTC (permalink / raw)
  To: Todd Fulton <pongo@jah.net>; +Cc: pgsql-performance

"Todd Fulton" <pongo@jah.net> writes:
> 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

The join itself is being done fine --- I doubt there is another option
that will go faster, given the difference in the table sizes.  Note the
join step completes in only 85 seconds.  What is killing you is the
sorting/grouping operation.  You could try increasing sort_mem to see
if that makes it go any faster, but I suspect the best answer would be to
update to PG 7.4.  7.4 will probably use hash aggregation for this and
avoid the sort altogether.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2004-02-17 23:55 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-17 17:06 long running query running too long Todd Fulton <pongo@jah.net>
2004-02-17 20:05 ` PC Drew <drewpc@ibsncentral.com>
2004-02-17 20:41   ` Todd Fulton <pongo@jah.net>
2004-02-17 23:17     ` PC Drew <drewpc@ibsncentral.com>
2004-02-17 23:55 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox