[postgis-users] Slow select from big table

[Mark Cave-Ayland]

On Wed, 2007-06-06 at 17:18 +0200, Matt Doughty wrote:
> Hi List,
> 
>  
> 
> I’ve got a simple question, how can I speed up this query? 
> 
>  
> 
> SELECT COUNT (*) FROM table1
> 
>  
> 
> Currently it’s taking a little more than five minutes to run on a
> table with 3.4 m rows. The table is gist indexed. Is it normal that
> Postgres takes so long?
> 
>  
> 
> Cheers,
> 
>  
> 
> Matt


Hi Matt,

First, if you haven't already, either perform a VACUUM FULL or CLUSTER
the table on the primary key in order to get rid of the dead space from
the heap.

Secondly, check your shared_buffer setting. What does "SHOW shared_buffers" give?
Given that 1 buffer is 8k, your shared_buffers should be set to around
25-30% of your available RAM.


Kind regards,

Mark.

-- 
ILande - Open Source Consultancy
http://www.ilande.co.uk