Questions about indexes with text_pattern_ops

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: Raw Message | Whole Thread | 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.

Responses

Browse pgsql-hackers by date

  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