Query plan w/ like clause question

Поиск
Список
Период
Сортировка
Искать
От
Steve Wranovsky
Тема
Query plan w/ like clause question
Дата
в 16:53:05
Msg-id
4.3.2.7.2.20020529122844.00d42c50@mail.merge.com
Список
Дерево обсуждения
Query plan w/ like clause question Steve Wranovsky <stevew@merge.com>
Re: Query plan w/ like clause question Richard Poole <rp@guests.deus.net>
Re: Query plan w/ like clause question Steve Wranovsky <stevew@merge.com>
Re: Query plan w/ like clause question Richard Poole <rp@guests.deus.net>
Hello,

I am having some strange results using a "like" clause under 7.2.1 w/ Linux.  I have a table with about 700,000 rows. When I select on one of the varchar fields that has an index created on it with an exact match, the results comes back quickly.  When I try to a like clause with a '%' wildcard that will return the same results as the exact match, the query does a sequential scan instead of using the index, and takes a significant amount of time to execute.  Below is the SQL to create the table & index, along with the query plans for the exact match, and the like clause.  Any suggestions on how to improve the "like" performance would be appreciated...  

Best regards,
Steve

Create Table Objects
   (SOP_Instance_UID VarChar(64) Not Null,
    Object_Entity_Type SmallInt Not Null,
    SOP_Class_UID VarChar(64) Not Null,
    Archived_Time Integer Not Null,
    Update_Time Integer Not Null,
    Object_Size Integer,
    Patient_ID VarChar(64),
    Study_Instance_UID VarChar(64),
    Series_Instance_UID VarChar(64),
    FileSet_UID VarChar(64),
    Primary Key (SOP_Instance_UID),
    Foreign Key (FileSet_UID) References FileSet (FileSet_UID),
    Foreign Key (Patient_ID) References Patient (Patient_ID),
    Foreign Key (Study_Instance_UID) References PatientStudy (Study_Instance_UID),
    Foreign Key (Series_Instance_UID) References Series (Series_Instance_UID)
   );

Create Index Objects_AK1
    On Objects (Patient_ID);

Here's the output from "explain analyze" and the total objects:

mergeark=# explain analyze select patient_id from objects where patient_id = '49.35.34.5.0';
NOTICE:  QUERY PLAN:

Index Scan using objects_ak1 on objects  (cost=0.00..118.06 rows=29 width=15) (actual time=58.30..117.52 rows=50 loops=1)
Total runtime: 117.75 msec

EXPLAIN


mergeark=# explain analyze select patient_id from objects where patient_id like '49.35.34.5.%';
NOTICE:  QUERY PLAN:

Seq Scan on objects  (cost=100000000.00..100057379.33 rows=1 width=15) (actual time=661.30..49266.06 rows=50 loops=1)
Total runtime: 49266.27 msec

EXPLAIN

mergeark=# select count(*) from objects;
 count  
--------
 698836
(1 row)

В списке pgsql-general по дате отправления
От: Vincent Stoessel
Дата:
От: Neil Conway
Дата:
FAQ