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

planner not using index for like operator

From: "Sriram Dandapani" <sdandapani(at)counterpane(dot)com>
To: "Pgsql-Performance \(E-mail\)" <pgsql-performance(at)postgresql(dot)org>
Subject: planner not using index for like operator
Date: 2006-04-25 17:08:02
Message-ID: 6992E470F12A444BB787B5C937B9D4DF0406B241@ca-mail1.cis.local (view raw or flat)
Thread:
Lists: pgsql-performance
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.

Responses

pgsql-performance by date

Next:From: PFCDate: 2006-04-25 17:53:15
Subject: Slow queries salad ;)
Previous:From: David WheelerDate: 2006-04-25 16:44:55
Subject: PL/pgSQL Loop Vs. Batch Update

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