Re: Poor performance on very simple query ?

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(at)postgresql(dot)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/