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 DB60BD1D064 for ; Mon, 19 Apr 2004 08:02:52 -0300 (ADT) 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 64527-01 for ; Mon, 19 Apr 2004 08:02:53 -0300 (ADT) Received: from kinetic.co.uk (mailhost.lei.co.uk [217.207.0.112]) by svr1.postgresql.org (Postfix) with ESMTP id 17557D1BAAE for ; Mon, 19 Apr 2004 08:02:50 -0300 (ADT) Received: from WorldClient ([217.207.0.112]) (authenticated user gerard.isdell@kinetic.co.uk) by kinetic.co.uk (kinetic.co.uk [217.207.0.112]) (MDaemon.PRO.v7.0.1.R) with ESMTP id md50000026316.msg for ; Mon, 19 Apr 2004 11:59:48 +0100 Received: from [217.207.0.3] via WorldClient with HTTP; Mon, 19 Apr 2004 11:59:48 +0100 Date: Mon, 19 Apr 2004 11:59:48 +0100 From: "Gerard Isdell" To: pgsql-performance@postgresql.org Subject: very slow simple query - outer join makes it quicker MIME-Version: 1.0 Content-Type: text/plain Message-ID: X-Mailer: WorldClient 7.0.1 X-Authenticated-Sender: gerard.isdell@kinetic.co.uk X-MDRemoteIP: 217.207.0.112 X-Return-Path: gerard.isdell@kinetic.co.uk X-MDaemon-Deliver-To: pgsql-performance@postgresql.org Reply-To: gerard.isdell@kinetic.co.uk X-Spam-Processed: kinetic.co.uk, Mon, 19 Apr 2004 11:59:49 +0100 X-MDAV-Processed: kinetic.co.uk, Mon, 19 Apr 2004 11:59:49 +0100 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/263 X-Sequence-Number: 6563 Hi Everyone I am new to this group and relatively new to Postgres, having used MSSQL 7 up until now. One table in my database in returning even the simplest of queries extremely slowly. The table is user table, and even the select userid from users takes over 20 seconds to run. There are about 2000 records in the table. The EXPLAIN ANALYZE on this table produces this output: Seq Scan on users (cost=0.00..89482.63 rows=1463 width=4) (actual time=68.836..40233.463 rows=1465 loops=1) Total runtime: 40234.965 ms SELECT USERID FROM USERS produces this: 1465 rows fetched (25.28 sec) The userid field is the primary key and has an index on it with this ddl: ALTER TABLE "public"."users" ADD CONSTRAINT "users_pkey" PRIMARY KEY ("userid"); There are other tables, such as the messages table, that have 10s of thousands of rows and they return records much more quickly. There must be something seriously wrong for simple queries like this to take so long. I should say that we are using the OpenFTS text search on the users table. In many cases to make the queries run at reasonable speeds I do an outer join on another table, and surprisingly these results come back very quickly Can anybody help me in diagnosing this problem. Gerard Isdell ************************************************************************* This e-mail and any attachments may contain confidential or privileged information. If you are not the intended recipient, please contact the sender immediately and do not use, store or disclose their contents. Any views expressed are those of the individual sender and not of Kinetic Information System Services Limited unless otherwise stated. www.kinetic.co.uk