Re: reducing random_page_cost from 4 to 2 to force index scan

Поиск
Список
Период
Сортировка
От Kevin Grittner
Тема Re: reducing random_page_cost from 4 to 2 to force index scan
Дата
Msg-id 4DB7C88A020000250003CF10@gw.wicourts.gov
обсуждение исходный текст
Ответы Re: reducing random_page_cost from 4 to 2 to force index scan  (Sok Ann Yap <sokann@gmail.com>)
Список pgsql-performance
Sok Ann Yap  wrote:
> Kevin Grittner  wrote:

>> Please show us your overall configuration and give a description
>> of the hardware (how many of what kind of cores, how much RAM,
>> what sort of storage system).

> Here's the configuration (this is just a low end laptop):

> version | PostgreSQL 9.0.4 on x86_64-pc-linux-gnu,
> compiled by GCC x86_64-pc-linux-gnu-gcc (Gentoo 4.5.2 p1.0,
> pie-0.4.5) 4.5.2, 64-bit
> checkpoint_segments | 16
> default_statistics_target | 10000

Usually overkill.  If this didn't help, you should probably change it
back.

> effective_cache_size | 512MB
> lc_collate | en_US.UTF-8
> lc_ctype | en_US.UTF-8
> listen_addresses | *
> log_destination | syslog
> log_min_duration_statement | 0
> maintenance_work_mem | 256MB
> max_connections | 100

You probably don't need this many connections.

> max_stack_depth | 2MB
> port | 5432
> random_page_cost | 4
> server_encoding | UTF8
> shared_buffers | 256MB
> silent_mode | on
> TimeZone | Asia/Kuala_Lumpur
> wal_buffers | 1MB
> work_mem | 32MB
> (20 rows)

It's hard to recommend other changes without knowing the RAM on the
system.  How many of what kind of CPUs would help, too.

> The thing is, the query I posted was fairly simple (I think), and
> PostgreSQL should be able to choose the 3000+ times faster index
> scan with the default random_page_cost of 4.

It picks the plan with the lowest estimated cost.  If it's not
picking the best plan, that's usually an indication that you need to
adjust cost factors so that estimates better model the actual costs.

> If I need to reduce it to 2 when using a 5.4k rpm slow disk, what
> is random_page_cost = 4 good for?

It's good for large databases with a lot of physical disk I/O.  In
fact, in some of those cases, it needs to be higher.  In your test,
the numbers indicate that everything was cached in RAM.  That makes
the effective cost very low.

Also, the odds are that you have more total cache space between the
shared_buffers and the OS cache than the effective_cache_size
setting, so the optimizer doesn't expect the number of cache hits
you're getting on index usage.

-Kevin

В списке pgsql-performance по дате отправления:

Предыдущее
От: Claudio Freire
Дата:
Сообщение: Re: reducing random_page_cost from 4 to 2 to force index scan
Следующее
От: Dhimant Patel
Дата:
Сообщение: Query Performance with Indexes on Integer type vs. Date type.