Query plan w/ like clause question

Поиск
Список
Период
Сортировка
От Steve Wranovsky
Тема Query plan w/ like clause question
Дата
Msg-id 4.3.2.7.2.20020529122844.00d42c50@mail.merge.com
обсуждение исходный текст
Ответы Re: Query plan w/ like clause question  (Richard Poole <rp@guests.deus.net>)
Re: Query plan w/ like clause question  (Richard Poole <rp@guests.deus.net>)
Список pgsql-general
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.
WhenI 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
querydoes a sequential scan instead of using the index, and takes a significant amount of time to execute.  Below is
theSQL to create the table & index, along with the query plans for the exact match, and the like clause.  Any
suggestionson 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
Дата:
Сообщение: size of units in postgresql.conf
Следующее
От: Neil Conway
Дата:
Сообщение: Re: size of units in postgresql.conf