Re: How to reduce impact of a query.

Teodor Sigaev wrote:
>> The machine in question is a 1GB Ram, AMD 64 with Raid 1 Sata disks.
>> Non standard parts of my postgresql.conf are as follows:
>> max_connections=100
>> shared_buffers=128MB
>> work_mem=4MB
>> maintenance_work_mem=256MB
>> max_fsm_pages=204800
>> max_fsm_relations=1500
>>
>> Any tips appreciated.
>
> Pls, show
> 1) effective_cache_size
> 2) The query
> 3) Output of EXPLAIN ANALYZE of query
>
effective_cache_size is set at 128MB (the default).

A simplified version of the query is as follows

select email_id from email where to_tsquery('default','hannah') @@ fts;

Bitmap Heap Scan on email (cost=12.50..80.25 rows=18 width=8) (actual
time=9073.878..39371.665 rows=6535 loops=1)
Filter: ('''hannah'''::tsquery @@ fts)
-> Bitmap Index Scan on email_fts_index (cost=0.00..12.49 rows=18
width=0) (actual time=9023.036..9023.036 rows=6696 loops=1)
Index Cond: ('''hannah'''::tsquery @@ fts)
Total runtime: 39375.892 ms

The time that this query takes is not the issue, rather it is the impact
that it has on the server - effectively killing it for the 40 seconds
due to the heavy disk access.