> 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.