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/
