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:35:05 -0700
User-Agent: KMail/1.8.3
References: <45224896.7040708@freesurf.fr>
	<3B2D7EC8-8E9B-46A3-A15F-4F554E10EA3B@purefiction.net>
In-Reply-To: <3B2D7EC8-8E9B-46A3-A15F-4F554E10EA3B@purefiction.net>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200610030835.06089.darcyb@commandprompt.com>

On October 3, 2006 05:08 am, Alexander Staubo wrote:
> On Oct 3, 2006, at 13:25 , Arnaud Lesauvage wrote:
> > 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);
>
> This is a very small table, but generally speaking, such queries
> benefit from an index; eg.,
>
>    create index table1_gid on table1 (gid);

gid is is a PRIMARY KEY, so it will already have an index in place.
>
> Note that PostgreSQL may still perform a sequential scan if it thinks
> this has a lower cost, eg. for small tables that span just a few pages.
>
> > I have run "VACUUM FULL" on this table many times... I don't know
> > what to try next !
>
> PostgreSQL's query planner relies on table statistics to perform
> certain optimizations; make sure you run "analyze table1".
>
> Alexander.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly

-- 
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/