Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 428896501EA for ; Wed, 12 Nov 2008 12:56:33 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 55368-07 for ; Wed, 12 Nov 2008 12:56:25 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.gransy.com (unknown [82.208.29.208]) by mail.postgresql.org (Postfix) with ESMTP id 2F1CC64FF5F for ; Wed, 12 Nov 2008 12:56:24 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by mail.gransy.com (Postfix) with ESMTP id 173F67AC0B6; Wed, 12 Nov 2008 17:56:22 +0100 (CET) Received: from mail.gransy.com ([127.0.0.1]) by localhost (nathalia.gransy.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 96SE38t1wUe4; Wed, 12 Nov 2008 17:56:21 +0100 (CET) Received: from anastacia.gransy.com (anastacia.gransy.com [89.187.130.100]) by mail.gransy.com (Postfix) with ESMTP id E692A7AC047; Wed, 12 Nov 2008 17:56:21 +0100 (CET) Received: from sq.gransy.com (localhost [127.0.0.1]) by anastacia.gransy.com (Postfix) with ESMTP id 0FD4831F86C; Wed, 12 Nov 2008 17:56:29 +0100 (CET) Received: from 89.102.139.23 (SquirrelMail authenticated user tv@fuzzy.cz) by sq.gransy.com with HTTP; Wed, 12 Nov 2008 17:56:29 +0100 (CET) Message-ID: <64051.89.102.139.23.1226508989.squirrel@sq.gransy.com> In-Reply-To: <838490.42593.qm@web34202.mail.mud.yahoo.com> References: <838490.42593.qm@web34202.mail.mud.yahoo.com> Date: Wed, 12 Nov 2008 17:56:29 +0100 (CET) Subject: Re: Performance Question From: tv@fuzzy.cz To: "- -" Cc: pgsql-performance@postgresql.org User-Agent: SquirrelMail/1.4.10a MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-2 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.1 tagged_above=0 required=5 tests=RDNS_NONE=0.1 X-Spam-Level: X-Archive-Number: 200811/94 X-Sequence-Number: 31825 > max_connections = 100 > shared_buffers = 16MB > work_mem = 64MB > everything else is set to the default OK, but what about effective_cache_size for example? Anyway, we need more information about the table itself - the number of rows is nice, but it does not say how large the table is. The rows might be small (say 100B each) or large (say several kilobytes), affecting the amount of data to be read. We need to know the structure of the table, and the output of the following commands: ANALYZE table; SELECT relpages, reltuples FROM pg_class WHERE relname = 'table'; EXPLAIN SELECT * FROM table; > > One of my tables has 660,000 records and doing a SELECT * from that table > (without any joins or sorts) takes 72 seconds. Ordering the table based on > 3 columns almost doubles that time to an average of 123 seconds. To me, > those numbers are crazy slow and I don't understand why the queries are > taking so long. The tables are UTF-8 encode and contain a mix of languages > (English, Spanish, etc). I'm running the query from pgadmin3 on a remote > host. The server has nothing else running on it except the database. > > As a test I tried splitting up the data across a number of other tables. I > ran 10 queries (to correspond with the 10 tables) with a UNION ALL to join > the results together. This was even slower, taking an average of 103 > seconds to complete the generic select all query. Well, splitting the tables just to read all of them won't help. It will make the problem even worse, due to the necessary processing (UNION ALL). regards Tomas