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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
