| 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: | 2007-08-29 17:51:41 |
| Message-ID: | 7014.1188409901@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Markus Benne | 2007-08-29 19:22:03 | Transaction Log |
| Previous Message | Mark Lewis | 2007-08-29 17:12:31 | Re: LIKE query verses = |