-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Tom Lane wrote: | Jeroen van Iddekinge <[EMAIL PROTECTED]> writes: | |>>You could tweak with several settings to get it to do an index scan |>>earlier, but these would probably break other queries. You don't need to |>>tune for 100 rows, morelike 100k or 100M. | | |>Which settings shoud I change for this? | | | I'd agree with John's response: if you change any settings based on just | this one test case, you're a fool. But usually random_page_cost is the | best knob to twiddle if you wish to encourage indexscans. |
Perhaps just a small comment - before starting the tuning process, you want to make sure the query planner has the right ideas about the nature of data contained in your indexed column.
Sometimes, if you insert reasonably sized batches of records containing the same value for that column (for example in a multicolumn key where you usually retrieve by only one column), statistics collector (used to) get out of sync with reality with regard to cardinality of data, because the default snapshot is too small to provide it with objective insight. If you're intimate with your data, you probably want to increase statistics target on that column and/or do some other statistics-related magic and ANALYZE the table again; that alone can mean the difference between a sequential and an index scan where appropriate, and most importantly, you don't need to distort the database's understanding of your hardware to achieve optimal plans (provided you have the value set to proper values, of course), so you won't get bitten where you don't expect it. :)
Again, this might not pertain to a 100-row table, but is a good thing [tm] to know when optimizing. I personally would prefer to look at that aspect of optimizer's understanding of data before anything else.
Hope this helps.
Regards, - -- Grega Bremec gregab at p0f dot net -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (GNU/Linux)
iD8DBQFCfvizfu4IwuB3+XoRAhI1AJ92uhoh0u9q7/XPllH37o5KXlpJdwCfQ+2b sJhq4ZWDdZU9x4APoGOsMes= =Tq99 -----END PGP SIGNATURE-----
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
