Re: [PERFORM] planner index choice
http://explain.depesz.com/s/br9 http://explain.depesz.com/s/gxH Well, I don't have time to do a thorough analysis right now, but in all the plans you've posted there are quite high values in the Rows x column (e.g. the 5727.5 value). That means a significant difference in estimated and actual row number, which may lead to poor choice of indexes etc. The planner may simply think the index is better due to imprecise statistics etc. Try to increase te statistics target for the columns, e.g. ALTER TABLE table ALTER COLUMN column SET STATISTICS integer where integer is between 0 and 1000 (the default value is 10 so use 100 or maybe 1000), run analyze and try to run the query again. Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Testing Sandforce SSD
On Wed, Jul 28, 2010 at 03:45:23PM +0200, Yeb Havinga wrote: Due to the LBA remapping of the SSD, I'm not sure of putting files that are sequentially written in a different partition (together with e.g. tables) would make a difference: in the end the SSD will have a set new blocks in it's buffer and somehow arrange them into sets of 128KB of 256KB writes for the flash chips. See also http://www.anandtech.com/show/2899/2 It's not a question of the hardware side, it's the software. The xlog needs to by synchronized, and the things the filesystem has to do to make that happen penalize the non-xlog disk activity. That's why my preferred config is xlog on ext2, rest on xfs. That allows the synchronous activity to happen with minimal overhead, while the parts that benefit from having more data in flight can do that freely. Mike Stone -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] planner index choice
t...@fuzzy.cz writes: http://explain.depesz.com/s/br9 http://explain.depesz.com/s/gxH Well, I don't have time to do a thorough analysis right now, but in all the plans you've posted there are quite high values in the Rows x column (e.g. the 5727.5 value). That means a significant difference in estimated and actual row number, which may lead to poor choice of indexes etc. The planner may simply think the index is better due to imprecise statistics etc. Yeah. The sq_ast_attr_val_attrid scan is a lot more selective than the planner is guessing (3378 rows estimated vs an average of 15 actual), and I think that is making the difference. If you look at the estimated row counts and costs, it's expecting that adding the second index will cut the number of heap fetches about 7x, hence saving somewhere around 4800 cost units in the heapscan step, more than it thinks the indexscan will cost. But taking 15 row fetches down to 2 isn't nearly enough to pay for the extra indexscan. Try to increase te statistics target for the columns, e.g. ALTER TABLE table ALTER COLUMN column SET STATISTICS integer It's worth a try but I'm not sure how much it'll help. A different line of attack is to play with the planner cost parameters. In particular, reducing random_page_cost would reduce the estimated cost of the heap fetches and thus discourage it from using the extra index. If you're working with mostly-cached tables then this would probably improve behavior overall, too. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.
introduce bugs. If we can get to the point where we have something to play around with, even if it's kind of kludgey or doesn't quite work, it'll give us some idea of whether further effort is worthwhile and how it should be directed. Should I put this on the TODO list, then, in hopes that someone steps forward? -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
