Re: slow select in big table - Mailing list pgsql-general

> shared_buffers = 810MB
> temp_buffers = 128MB
> work_mem = 512MB
> maintenance_work_mem = 256MB
> max_stack_depth = 7MB
> effective_cache_size = 800MB

QUERY PLAN without changes
Aggregate  (cost=98018.96..98018.97 rows=1 width=4) (actual
time=64049.326..64049.328 rows=1 loops=1)
  ->  Bitmap Heap Scan on tbl_photos_keywords  (cost=533.23..97940.02
rows=31577 width=4) (actual time=157.787..63905.939 rows=119154
loops=1)
        Recheck Cond: (keyword_id = 14)
        ->  Bitmap Index Scan on keyword_id  (cost=0.00..525.33
rows=31577 width=0) (actual time=120.876..120.876 rows=119154 loops=1)
              Index Cond: (keyword_id = 14)

Total runtime: 64049.686 ms
--------------------------------------------
shared_buffers = 810MB
temp_buffers = 128MB
work_mem = 12MB
maintenance_work_mem = 256MB
max_stack_depth = 7MB
enable_seqscan = off
seq_page_cost = 1.0
random_page_cost = 1.0
effective_cache_size = 800MB

QUERY PLAN with changes
Aggregate  (cost=30546.30..30546.31 rows=1 width=4) (actual
time=1710.100..1710.102 rows=1 loops=1)
  ->  Index Scan using keyword_id on tbl_photos_keywords
(cost=0.00..30467.36 rows=31577 width=4) (actual time=0.150..1573.843
rows=119154 loops=1)
        Index Cond: (keyword_id = 14)

Total runtime: 1710.185 ms

What else can be changed ?
Thx for help.