Skip site navigation (1)
Skip section navigation (2)
Peripheral Links
Header And Logo
|
Site Navigation
Re: LIKE query verses =
- From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
- To: Mark Lewis <mark(dot)lewis(at)mir3(dot)com>
- Cc: Karthikeyan Mahadevan <karthikeyan(dot)mahadevan(at)in(dot)ibm(dot)com>, pgsql-performance(at)postgresql(dot)org
- Subject: Re: LIKE query verses =
- Date: Wed, 29 Aug 2007 13:51:41 -0400
- Message-id: <7014.1188409901@sss.pgh.pa.us> <text/plain>
Mark Lewis <mark(dot)lewis(at)mir3(dot)com> writes: > What you probably want to do is tell PG that you're looking for a > literal underscore and not for any matching character by escaping the > underscore, that will allow it to do a much quicker index scan. The other half of the problem is that the planner is drastically misestimating the number of matching rows --- it thinks only one when there are really about a thousand, and this leads it to use a nestloop that will be very inefficient with so many rows. Try increasing the statistics target for that column. Also, if this is a pre-8.2 PG release, consider upgrading; I believe we improved the LIKE estimator in 8.2. regards, tom lane
- References:
- LIKE query verses =
- From: Karthikeyan Mahadevan
- Re: LIKE query verses =
- From: Mark Lewis
- LIKE query verses =
- Prev by Date: Re: LIKE query verses =
- Next by Date: Re: Shared memory usage
- Previous by thread: Re: LIKE query verses =
- Next by thread: Transaction Log
- Indexes: