Indeed, setting random_page_cost does the trick. Thanks!
It seems to make sense to set random_page_cost to this value. Are there any drawbacks?
postgresql-7.3.4
postgresql.conf:
tcpip_socket = true max_connections = 100 superuser_reserved_connections = 2
# Performance # shared_buffers = 12000 sort_mem = 8192 vacuum_mem = 32768 effective_cache_size = 64000 random_page_cost = 2
...
--On söndag, september 28, 2003 14.34.25 -0700 Josh Berkus <[EMAIL PROTECTED]> wrote:
Palle,
I have a SQL statement that I cannot get to use the index. postgresql insists on using a seqscan and performance is very poor. set enable_seqscan = true boost performance drastically, as you can see below. Since seqscan is not always bad, I'd rather not turn it off completely, but rather get the planner to do the right thing here. Is there another way to do this, apart from setting enable_seqscan=false?
In your postgresql.conf, try setting effective_cache_size to something like 50% of your system's RAM, and lovering random_page_cost to 2.0 or even 1.5. Then restart PostgreSQL and try your query again.
What version, btw?
-- Josh Berkus Aglio Database Solutions San Francisco
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
