Re: Many DataFileRead - IO waits

Поиск
Список
Период
Сортировка
От Michael Lewis
Тема Re: Many DataFileRead - IO waits
Дата
Msg-id CAHOFxGrWoQ+o=aDYkeS50n9bgjJn2FCFrFXSsx5DbgUj=mh9Wg@mail.gmail.com
обсуждение исходный текст
Ответ на Many DataFileRead - IO waits  (Ben Snaidero <bensnaidero@geotab.com>)
Ответы Re: Many DataFileRead - IO waits  (Ben Snaidero <bensnaidero@geotab.com>)
Список pgsql-performance
How big is ix_tabledata_intid_timestampdate_intotherid3_intotherid2 on disk? If you create another index with same fields, how much space does it take? Real question- are you vacuuming aggressively enough for your workload? Your index name seems to indicate that intotherid3 would be the third key, and yet the planner chose not to scan that deep and instead filtered after it found the relevant tuples based on intid and timestampdate. That seems peculiar to me.

The documentation discourages multi-column indexes because they have limited application unless the same fields are always used. Personally, I don't love reviewing the stats of indexscans or how many tuples were fetched and having to guess how deeply the index was scanned for the various queries involved.

I'd wonder if an index on only intid_timestampdate would be both much smaller and also have a more right-leaning pattern of information being added and accessed in terms of keeping frequently needing blocks in shared buffers.

As a side note, that planning time seems high to me for such a simple query. Have you increased default_statistics_target significantly perhaps?

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

Предыдущее
От: Ben Snaidero
Дата:
Сообщение: Many DataFileRead - IO waits
Следующее
От: Ben Snaidero
Дата:
Сообщение: Re: Many DataFileRead - IO waits