Re: Poor performance on very simple query ? - Mailing list pgsql-performance

From Alexander Staubo
Subject Re: Poor performance on very simple query ?
Date
Msg-id 3B2D7EC8-8E9B-46A3-A15F-4F554E10EA3B@purefiction.net
Whole thread Raw
In response to Poor performance on very simple query ?  (Arnaud Lesauvage <thewild@freesurf.fr>)
Responses Re: Poor performance on very simple query ?  (Darcy Buskermolen <darcyb@commandprompt.com>)
List pgsql-performance
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);

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.

pgsql-performance by date:

Previous
From: Arnaud Lesauvage
Date:
Subject: Re: Poor performance on very simple query ?
Next
From: Tobias Brox
Date:
Subject: Re: Poor performance on very simple query ?