Skip site navigation (1) Skip section navigation (2)

Re: planner not using index for like operator

From: "Dave Dutcher" <dave(at)tridecap(dot)com>
To: "'Sriram Dandapani'" <sdandapani(at)counterpane(dot)com>,"'Pgsql-Performance \(E-mail\)'" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: planner not using index for like operator
Date: 2006-04-25 18:03:13
Message-ID: 000001c66892$8583d040$ (view raw or whole thread)
Lists: pgsql-performance
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:

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Sriram
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

In response to

pgsql-performance by date

Next:From: Bill MoranDate: 2006-04-25 18:14:35
Subject: Large (8M) cache vs. dual-core CPUs
Previous:From: PFCDate: 2006-04-25 17:53:15
Subject: Slow queries salad ;)

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group