Re: sequence scan on PK

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane wrote:
| Jeroen van Iddekinge <iddekingej(at)lycos(dot)com> 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-----