Re: Poor performance on very simple query ?

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/