[rearranging to put related information together] norn Since the LIMIT 3 and LIMIT 4 queries generated exactly the same plan, the increased time for LIMIT 4 suggests that there are 3 matching rows which are near the end of the index it is scanning, but the fourth one is much farther in. Since what you're showing suggests that the active portion of your data is heavily cached, you might benefit from decreasing random_page_cost, and possibly also seq_page_cost. > 8GB RAM > effective_cache_size = 1536MB > Please also note that this hardware isn't dedicated DB server, but > also serve as web server and file server. Even with those other uses, you're likely to actually be using 6 GB or 7 GB for cache. I'd set effective_cache_size in that range. > max_connections = 250 > work_mem = 128MB While probably not related to this problem, that's a dangerous combination. What if all 250 connections are active with a query which uses work_mem memory? A single connection can actually be using several work_mem allocations at once. > 2 SATA 750GB (pg db installed in software RAID 0) You do realize that if either drive dies you lose all your data on that pair of drives, right? I hope the value of the data and well tested backup procedures keeps the loss to something which is acceptable. > I have about 3 million rows in core_object, 1.5 million in > plugin_plugin_addr and 1.5 million in plugins_guide_address. > When there were 300 000+ objects queries works perfectly, but as db > enlarge things go worse... With a relational database, it's not unusual for the most efficient plan to depend on the quantity of data in the tables. It is important that your statistics are kept up-to-date so that plans can adapt to the changing table sizes or data distributions. The effective_cache_size and cost parameters are also used to calculate the costs of various plans, so adjusting those may help the optimizer make good choices. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance