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 [EMAIL PROTECTED]
