From: Darcy Buskermolen <darcyb@commandprompt.com>
Organization: CommandPrompt, Inc
To: pgsql-performance@postgresql.org
Subject: Re: Poor performance on very simple query ?
Date: Tue, 3 Oct 2006 08:10:23 -0700
User-Agent: KMail/1.8.3
Cc: Arnaud Lesauvage <thewild@freesurf.fr>
References: <45224896.7040708@freesurf.fr>
In-Reply-To: <45224896.7040708@freesurf.fr>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200610030810.24197.darcyb@commandprompt.com>

On October 3, 2006 04:25 am, Arnaud Lesauvage wrote:
> 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 !

According to your explain analyze, it's taking 0.8 of a milisecond (less than 
1 1000th of a second) so I can't see how this can possibly be speed up.

> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

-- 
Darcy Buskermolen
Command Prompt, Inc.
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997
http://www.commandprompt.com/