Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id BE4E69FB2F4 for ; Tue, 3 Oct 2006 08:54:04 -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 09038-07 for ; Tue, 3 Oct 2006 08:54:00 -0300 (ADT) X-Greylist: delayed 00:28:45.225081 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 547A19FB2C1 for ; Tue, 3 Oct 2006 08:54:01 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from serveurmail01.codata.be (125.123.146.82.ipv4.evonet.be [82.146.123.125]) by svr4.postgresql.org (Postfix) with ESMTP id 71C805AF04F for ; Tue, 3 Oct 2006 11:25:13 +0000 (GMT) Received: from localhost (localhost.localdomain [127.0.0.1]) by serveurmail01.codata.be (Postfix) with ESMTP id 2E093A8003 for ; Tue, 3 Oct 2006 13:25:11 +0200 (CEST) X-DSPAM-Result: Innocent X-DSPAM-Processed: Tue Oct 3 13:25:10 2006 X-DSPAM-Confidence: 0.9996 X-DSPAM-Probability: 0.0000 X-DSPAM-Signature: 45224896327522066714844 X-DSPAM-Factors: 27, X-Virus-Scanned: amavisd-new at Received: from serveurmail01.codata.be ([127.0.0.1]) by localhost (serveurmail01.codata.be [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id C+caUpNq8T0E for ; Tue, 3 Oct 2006 13:25:10 +0200 (CEST) Received: from [192.168.1.201] (unknown [192.168.1.201]) by serveurmail01.codata.be (Postfix) with ESMTP id 71AABA8001 for ; Tue, 3 Oct 2006 13:25:10 +0200 (CEST) Message-ID: <45224896.7040708@freesurf.fr> Date: Tue, 03 Oct 2006 13:25:10 +0200 From: Arnaud Lesauvage User-Agent: Thunderbird 1.5.0.7 (Windows/20060909) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Poor performance on very simple query ? Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.335 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200610/29 X-Sequence-Number: 21158 Hi List ! I have a performance problem, but I am not sure whether it really is a problem or not. I am running a fresh install of PostgreSQL 8.1.4 on Windows2000. The server is a bi-opteron with 2GB of RAM. The PostgreSQL's data folder is on a RAID-0 array of 2 SATA WD Raptor drives (10.000 rpm, 8MB cache). I have a very simple table, with only ~500 rows : CREATE TABLE table1 ( gid int4 NOT NULL DEFAULT 0, field1 varchar(45) NOT NULL, field2 int2 NOT NULL DEFAULT 1, field3 int2 NOT NULL DEFAULT 0, field4 int2 NOT NULL DEFAULT 1, field5 int4 NOT NULL DEFAULT -1, field6 int4, field7 int4, field8 int4, field9 int2 DEFAULT 1, CONSTRAINT table1_pkey PRIMARY KEY (gid) ) WITHOUT OIDS; The problem is that simple select queries with the primary key in the WHERE statement take very long to run. For example, this query returns only 7 rows and takes about 1 second to run ! SELECT * FROM table1 WHERE gid in (33,110,65,84,92,94,13,7,68,41); EXPLAIN ANALYZE SELECT * FROM table1 WHERE gid in (33,110,65,84,92,94,13,7,68,41); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------ Seq Scan on table1 (cost=0.00..23.69 rows=10 width=35) (actual time=0.023..0.734 rows=7 loops=1) Filter: ((gid = 33) OR (gid = 110) OR (gid = 65) OR (gid = 84) OR (gid = 92) OR (gid = 94) OR (gid = 13) OR (gid = 7) OR (gid = 68) OR (gid = 41)) Total runtime: 0.801 ms (3 rows) I have run "VACUUM FULL" on this table many times... I don't know what to try next ! What is wrong here (because I hope that something is wrong) ? Thanks a lot for your help ! Regards -- Arnaud