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 588BD9FB1E8 for ; Fri, 7 Jul 2006 16:18:26 -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 33333-01 for ; Fri, 7 Jul 2006 16:18:22 -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 741D49FB1D4 for ; Fri, 7 Jul 2006 16:18:22 -0300 (ADT) Received: from [64.213.65.29] (helo=[127.0.0.1]) by he100.hostelement.com with esmtpa (Exim 4.52) id 1FyvqB-0001uQ-U3 for pgsql-sql@postgresql.org; Fri, 07 Jul 2006 15:18:17 -0400 Message-ID: <44AEB375.9070804@oisinglynn.com> Date: Fri, 07 Jul 2006 15:18:13 -0400 From: Oisin Glynn User-Agent: Thunderbird 1.5.0.4 (Windows/20060516) MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Help with performance and explain. 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/113 X-Sequence-Number: 25687 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