Re: 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