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
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