Re: planner not using index for like operator

["Dave Dutcher" <dave@xxxxxxxxxxxx>]

Title: Message

If you are using a locale other than the C locale, you need to create the index with an operator class to get index scans with like.

 

See here for details:

 

http://www.postgresql.org/docs/8.1/interactive/indexes-opclass.html

 

-----Original Message-----
From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Sriram Dandapani
Sent: Tuesday, April 25, 2006 12:08 PM
To: Pgsql-Performance (E-mail)
Subject: [PERFORM] planner not using index for like operator

For the query

 

 

Select col1 from table1

Where col1 like ‘172.%’

 

The table has 133 million unique ip addresses. Col1 is indexed.

 

The optimizer is using a sequential scan

 

This is the explain analyze output

 

"Seq Scan on table1 (cost=0.00..2529284.80 rows=1 width=15) (actual time=307591.339..565251.775 rows=524288 loops=1)"

"  Filter: ((col1)::text ~~ '172.%'::text)"

"Total runtime: 565501.873 ms"

 

 

The number of affected rows (500K) is a small fraction of the total row count.