Hi Denis,
> The kind of requests that I am really interested in are:
> select count(*) from table where table.column like 'pattern%'
> These seems to go much master on mysql (which I guess it not a MVCC database? or wasn't
> the Innobase supposed to make it so?), than on postgresql.
A few things.
* MVCC in PostgreSQL allows us to be way faster than MySQL when you have heaps of concurrent readers and writers. The
tradeoffis that count(*) is slow since PostgreSQL needs to check that each tuple is actually visible to your query (eg.
youstart a transaction, somone else inserts a row, you do a count(*) - should the result include that new row or not?
Answer:no.)
* Just avoid doing count(*) over the entire table with no where clause!!! It's as easy as that
* The LIKE 'pattern%' is indexable in Postgresql. You will need to create a normal btree index over table.column. So
longas the index is returning a small portion of the table (eg. say only 5-10% of the fields begin with pattern), then
theindex will be used and it will be fast.
* If you want really fast full text indexing, check out contrib/tsearch - it's really, really, really fast.
Chris