Guy Deleeuw wrote:
My hardware : lapto hp envy I7 processor with a ssd disk
> Memory :
> total used free shared buffers cached
> Mem: 8129356 2866516 5262840 0 387172 948132
> -/+ buffers/cache: 1531212 6598144
> Swap: 15625208 0 15625208
It looks like everything you're running, including caching the entire
database, or at least the active part of it, fits in less than 3 GB
on your 8 GB machine.
> shared_buffers = 24MB
Given your hardware, I would bump that to 2GB or so.
I would also set effective_cache_size to 7GB. I would set work_mem
to at *least* 50MB; if you're only using a few connections, you could
easily bump that to 100MB to 200MB. Fully cached, especially if it's
all on PostgreSQL shared memory, seq_page_cost and random_page_cost
both to 0.05. Between the SSD and having all data in shared memory,
you might want to go even lower. There is some other general tuning
to be done, but the above will make the most difference for read-only
queries, which is what you're asking about.
[nested views, using subqueries]
You should probably look at reqorking the v_stock view to use joins
instead of subqueries -- they often optimize much better.
I didn't spend a lot of time with the EXPLAIN ANALYZE output, because
if you configure PostgreSQL to get costing factors more in line with
actual costs for that hardware, you're likely to see totally
different plans. And I can pretty much guarantee that if you tune
the costs *and* rework that view to use JOINs, you'll get completely
different plans which will perform *much* better.
I hope this helps.
-Kevin