X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id DE3FD9FB215
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  7 Jul 2006 18:15:12 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 88410-06 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  7 Jul 2006 18:15:08 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from he100.hostelement.com (unknown [64.246.26.51])
	by postgresql.org (Postfix) with ESMTP id B18EF9FB1F5
	for <pgsql-sql@postgresql.org>; Fri,  7 Jul 2006 18:15:08 -0300 (ADT)
Received: from [64.213.65.29] (helo=[127.0.0.1])
	by he100.hostelement.com with esmtpa (Exim 4.52)
	id 1FyxfE-0003Ly-7y; Fri, 07 Jul 2006 17:15:04 -0400
Message-ID: <44AECED5.3040302@oisinglynn.com>
Date: Fri, 07 Jul 2006 17:15:01 -0400
From: Oisin Glynn <me@oisinglynn.com>
User-Agent: Thunderbird 1.5.0.4 (Windows/20060516)
MIME-Version: 1.0
To: Oisin Glynn <me@oisinglynn.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Help with performance and explain.
References: <44AEB375.9070804@oisinglynn.com>
In-Reply-To: <44AEB375.9070804@oisinglynn.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-AntiAbuse: This header was added to track abuse,
	please include it with any abuse report
X-AntiAbuse: Primary Hostname - he100.hostelement.com
X-AntiAbuse: Original Domain - postgresql.org
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - oisinglynn.com
X-Source: 
X-Source-Args: 
X-Source-Dir: 
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200607/121
X-Sequence-Number: 25695

Oisin Glynn wrote:
> I have an issue with a select returning very slowly  approx 198 
> seconds. I took a backup of this DB and restored it on another system 
> and it is returning in 28 seconds.
>
> Windows 2000  PG Version 8.0.3  Data is inserted into the table row by 
> row.
> Table has index on service
> explain SELECT
>    callrecord."service", callrecord."timequeuing", 
> callrecord."timeconversation", callrecord."timeoffering", 
> callrecord."calltype", callrecord."application"
> FROM
>    "public"."callrecord" callrecord
> ORDER BY
>    callrecord."service" ASC
>
> "Sort  (cost=284032.83..284322.17 rows=115734 width=46)"
> "  Sort Key: service"
> "  ->  Seq Scan on callrecord  (cost=0.00..270144.34 rows=115734 
> width=46)"
>
>
> Windows 2000 Version 8.0.1  DB created from backup of other server.
> explain SELECT
>    callrecord."service", callrecord."timequeuing", 
> callrecord."timeconversation", callrecord."timeoffering", 
> callrecord."calltype", callrecord."application"
> FROM
>    "public"."callrecord" callrecord
> ORDER BY
>    callrecord."service" ASC
> "Sort  (cost=17465.33..17754.65 rows=115729 width=46)"
> "  Sort Key: service"
> "  ->  Seq Scan on callrecord  (cost=0.00..3577.29 rows=115729 width=46)"
>
> Any help would be greatly appreciated.
>
> Oisin
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq

I decided to Import a dump into the same server that was displaying the 
slow response and it was fine on the second database.... Explain below. 
So I am even more puzzled but I guess my immediate issue is over.   I 
had vacuumed and analyzed this pre dumping to no avail on the initial db.

"Sort  (cost=31284.82..31576.38 rows=116622 width=234)"
"  Sort Key: service"
"  ->  Seq Scan on callrecord  (cost=0.00..3605.22 rows=116622 width=234)"

Oisin