Re: planner not using index for like operator
| От | Dave Dutcher |
|---|---|
| Тема | Re: planner not using index for like operator |
| Дата | |
| Msg-id | 000001c66892$8583d040$8300a8c0@tridecap.com обсуждение исходный текст |
| Ответ на | planner not using index for like operator ("Sriram Dandapani" <sdandapani@counterpane.com>) |
| Список | 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@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] 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 operatorFor 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.
В списке pgsql-performance по дате отправления: