From: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
To: denis@next2me.com
To: josh@agliodbs.com
To: pgsql-performance@postgresql.org
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used
Date: Wed, 9 Apr 2003 10:35:22 +0800
Message-ID: <077201c2fe40$ab420d70$6500a8c0@fhp.internal> (raw)
References: <EKEBJNAJDPKJBDFGJNIJAENMDBAA.denis@next2me.com>
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
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-sql@postgresql.org
Cc: chriskl@familyhealth.com.au, denis@next2me.com, josh@agliodbs.com, pgsql-performance@postgresql.org
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used
In-Reply-To: <077201c2fe40$ab420d70$6500a8c0@fhp.internal>
* 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