Re: Improve Seq scan performance

От: PFC
Тема: Re: Improve Seq scan performance
Дата: ,
Msg-id: op.ukpvoi1lcigqcu@soyouz
(см: обсуждение, исходный текст)
Ответ на: Improve Seq scan performance  (Lutischán Ferenc)
Список: pgsql-performance

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

Improve Seq scan performance  (Lutischán Ferenc, )
 Re: Improve Seq scan performance  (Craig Ringer, )
  Re: Improve Seq scan performance  ("Vladimir Sitnikov", )
   Re: Improve Seq scan performance  (Craig Ringer, )
    Re: Improve Seq scan performance  ("Vladimir Sitnikov", )
     Re: Improve Seq scan performance  (Craig Ringer, )
   Re: Improve Seq scan performance  (Lutischán Ferenc, )
 Re: Improve Seq scan performance  (PFC, )
 Re: Improve Seq scan performance  (PFC, )

> Dear List,
>
> I would like to improve seq scan performance. :-)
>
> I have many cols in a table. I use only 1 col for search on it.  It is
> indexed with  btree with text_pattern_ops. The search method is: r like
> '%aaa%'
> When I make another table with only this col values, the search time is
> better when the data is cached. But wronger when the data isn't in cache.
>
> I think the following:
> - When there is a big table with many cols, the seq search is read all
> cols not only searched.
> - If we use an index with full values of a col, it is possible to seq
> scan over the index is make better performance (lower io with smaller
> data).
>
> It is possible to make an index on the table, and make a seq index scan
> on this values?

    You can fake this (as a test) by creating a separate table with just your
column of interest and the row id (primary key), updated via triggers, and
seq scan this table. Seq scanning this small table should be fast. Of
course if you have several column conditions it will get more complex.

    Note that btrees cannot optimize '%aaa%'. You could use trigrams.


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

От: PFC
Дата:
Сообщение: Re: Performance Question
От: "Віталій Тимчишин"
Дата:
Сообщение: Re: PostgreSQL OR performance