PostgreSQL Indexes: First principles

Re:PostgreSQL Indexes: First principles by atomic77 on Friday February 26, 2016 @05:20PM (#11184733)

AFAICT pgsql controls this with the random_page_cost and seq_page_cost variables [1], which are relative and seem to default to 4 and 1 respectively. It doesn't look like these are automatically adjusted though based on the hardware.

Some searching and I found some blog posts suggesting lowering them both to closer to one on SSDs [2]. Indeed, the documentation says you can set them both to 1 if you are sure that the database will be fully cached in RAM.

[1] http://www.postgresql.org/docs/current/static/runtime-config... [2] http://www.cybertec.at/2013/01/better-postgresql-performance...

[ Parent | on HN ]

Re:PostgreSQL Indexes: First principles by wfn on Friday February 26, 2016 @05:36PM (#11184815)

Aha, yeah I recall reading suggestions akin to those for SSDs. Nice!

There's also `ALTER TABLE SET STATISTICS` and default_statistics_target config parameter (for supposedly smart query planning on the fly?) (though would have to re-read to recall how these things work - off to bed soon): http://www.postgresql.org/docs/current/static/runtime-config... (edit ah you included the same link - lots of good stuff here - ahh I like Postgres documentation)

[ Parent | on HN ]