Re: Query uses incorrect index

От: Kevin Grittner
Тема: Re: Query uses incorrect index
Дата: ,
Msg-id: 4D1076EF020000250003888E@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: Query uses incorrect index  (pasman pasmański)
Список: pgsql-performance

Скрыть дерево обсуждения

Query uses incorrect index  (pasman pasmański, )
 Re: Query uses incorrect index  ("Kevin Grittner", )
 Re: Query uses incorrect index  (pasman pasmański, )
  Re: Query uses incorrect index  (Guillaume Cottenceau, )
  Re: Query uses incorrect index  (Tom Lane, )
 Re: Query uses incorrect index  ("Kevin Grittner", )
 Re: Query uses incorrect index  (pasman pasmański, )

pasman pasma*ski<> wrote:

>                     ->  Index Scan using NTA_5" on "NumeryA" a
> (cost=0.01..10016.75 rows=24175 width=42) (actual
> time=0.132..308.018 rows=33597 loops=1)"

> seq_page_cost = 0.3
> random_page_cost = 0.5

Your data is heavily cached (to be able to read 33597 rows randomly
through an index in 308 ms), yet you're telling the optimizer that a
random access is significantly more expensive than a sequential one.
Try this in your session before running the query (with all indexes
present):

set seq_page_cost = 0.1;
set random_page_cost = 0.1;

I don't know if the data for all your queries is so heavily cached
-- if so, you might want to change these settings in your
postgresql.conf file.

-Kevin


В списке pgsql-performance по дате сообщения:

От: Merlin Moncure
Дата:
Сообщение: Re: MySQL HandlerSocket - Is this possible in PG?
От: Mladen Gogala
Дата:
Сообщение: Re: Performance of PostgreSQL over NFS