From: PFC <lists@peufeu.com>
To: Lutischán Ferenc <lutischanf@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Improve Seq scan performance
Date: Sun, 16 Nov 2008 15:54:08 +0100
Message-ID: <op.ukpvoi1lcigqcu@soyouz> (raw)
In-Reply-To: <4917D9C3.9060001@gmail.com>
References: <4917D9C3.9060001@gmail.com>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, lutischanf@gmail.com
Subject: Re: Improve Seq scan performance
In-Reply-To: <op.ukpvoi1lcigqcu@soyouz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox