| From: | "Kaare Rasmussen" <kaare(at)jasonic(dot)dk> |
|---|---|
| To: | pgsql-hackers(at)postgresql(dot)org |
| Subject: | Questions about indexes with text_pattern_ops |
| Date: | 2008-02-25 13:08:34 |
| Message-ID: | courier.47C2BDD2.00000E94@mail.webline.dk |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi
The database is initialized with utf8, so in order for LIKE to use the index
on a text field, I used text_pattern_ops when I created it. So far so good.
It's in the documentation, but there's no explanation of why this index will
only work for LIKE searches. How come that I have to have two different
indexes if I want to give Postgres the ability to choose index scan over seq
scan on LIKE and non-LIKE searches?
Is it a performance issue?
Also, when I tried to create the index as a partial one (avoiding the 95%
entries with empty strings), Postgresql chooses to use seq scan. This sounds
counter intuitive to me.
CREATE INDEX new_index ON a (b text_pattern_ops) WHERE b <> '';
This is 8.2.6.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Mark Mielke | 2008-02-25 13:47:23 | Smaller db in 8.3 (was: Re: insert ... delete ... returning ... ?) |
| Previous Message | Florian G. Pflug | 2008-02-25 10:52:43 | Re: dblink doesn't honor interrupts while waiting a result |