Re: [SQL] Yet Another (Simple) Case of Index not used

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 tradeoff is that count(*) is slow since PostgreSQL needs to check that each tuple is actually visible to your query (eg. you start 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 long as the index is returning a small portion of the table (eg. say only 5-10% of the fields begin with pattern), then the index 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