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 A7805D1D6B9 for ; Tue, 17 Feb 2004 20:42:10 +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 35922-04 for ; Tue, 17 Feb 2004 16:42:05 -0400 (AST) Received: from sccrmhc13.comcast.net (sccrmhc13.comcast.net [204.127.202.64]) by svr1.postgresql.org (Postfix) with ESMTP id 19C9AD1D69B for ; Tue, 17 Feb 2004 16:42:03 -0400 (AST) Received: from juarez (c-67-164-12-23.client.comcast.net[67.164.12.23]) by comcast.net (sccrmhc13) with SMTP id <20040217204154016003b6nme> (Authid: the_pongo@comcast.net); Tue, 17 Feb 2004 20:41:55 +0000 From: "Todd Fulton" To: "'PC Drew'" Cc: Subject: Re: long running query running too long Date: Tue, 17 Feb 2004 12:41:51 -0800 Message-ID: <00b401c3f596$78f92800$326aa8c0@juarez> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 In-Reply-To: <99C04A9E-6184-11D8-9183-000393BDC2FE@ibsncentral.com> 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/196 X-Sequence-Number: 5720 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=3D# \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=3D# \d idx_spk_tgp_tgpid Index "idx_spk_tgp_tgpid" Column | Type ---------------+----------------------- tgpid | bigint directoryname | character varying(64) btree spank_prod=3D# \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=3D# \d idx_spk_tgplog_createdate Index "idx_spk_tgplog_createdate" Column | Type ------------+-------------------------- createdate | timestamp with time zone btree spank_prod=3D# \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=92ve got a table with about 8 million rows and growing.=A0 I must run= =20 > reports daily off this table, and another smaller one.=A0 Typical query= =20 > =96 joins, groupings and aggregates included.=A0 This certain report takes > about 10 minutes on average and is getting longer.=A0 I=92ve created all= =20 > the indices I think are necessary. > > What indexes have you created? The query is not using any indexes, so=20 there might be a problem there. Can you disable seqscans temporarily=20 to test this? > > prod=3D# explain analyze SELECT t.tgpid, t.directoryname, t.templateid,= =20 > count(*) AS requested FROM (spk_tgp t JOIN spk_tgplog l ON ((t.tgpid =3D > l.tgpid))) GROUP BY t.tgpid, t.directoryname, t.templateid; Can you please send the results of the following commands: psql=3D# \d spk_tgp and psql=3D# \d spk_tgplog You might also want to try using a sub-query instead of a join. I'm=20 assuming that the spk_tgplog table has a lot of rows and spk_tgp has=20 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=20 tgpid) r, spk_tgp t WHERE r.tgpid =3D t.tgpid; -- PC Drew ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org