David Garamond说：

使用索引可能有助于此查询的性能，如果是，我该如何强制Postgres使用索引？

DB1 => select * from t where lower（f）like''mmm％'';

我怀疑你在列上指定了较低的函数

数据，即lower（f），暗示该函数必须应用于表中的每个

行，以便在测试之前计算该值

条件。


我不知道你能做什么，也不能做PG中的索引方式，

条款根据

列的计算（上/下）值创建索引。


但您可以考虑在表中添加一个额外的列触发器

，以便触发器将列UP或b $ b的UPPER或LOWER版本放入新列。


喜欢搜索然后是
select * from t其中new_upper_f喜欢上层（''MMM％''）;


如果新列上有一个索引new_upper_f，你应该

避免全表扫描。 （我想，我还没有测试过这个）...


John Sidney-Woollett

---------- -----------------（广播结束）---------------------------

提示6：您是否搜索了我们的列表档案？

http://archives.postgresql.org

试试这个：

CREATE [UNIQUE] INDEX my_index ON t（lower（f））;


John Sidney-Woollett写道：

David Garamond说：
>

使用索引可能有助于此查询的性能，如果是，我如何强制Postgres使用索引？

db1 =&gt ; select * from t where lower（f）like''mmm％'';

我怀疑你在列上指定了较低的函数
我不知道你能做什么，也不能做PG中的索引方式，在创建基于计算（上/下）值的索引的条件中
列。

但您可以考虑在表格中添加一个额外的列和一个触发器
，以便触发器将列的UP或LOWER版本放入f
新专栏。

喜欢搜索，然后选择*来自t，其中new_upper_f喜欢上层（''MMM％''）;

如果新列new_upper_f上有索引，则应该避免全表扫描。 （我想，我还没有测试过这个）......

John Sidney-Woollett

-------------- -------------（广播结束）---------------------------
提示6：您是否搜索了我们的列表档案？

http：//archives.postgresql .org

---------------------------（广播结束）---------------------------

提示8：解释分析是你的朋友>

John Sidney-Woollett说：

select * from t其中new_upper_f喜欢上层（''MMM％''）;

我想我的意思


select * from t其中new_upper_f喜欢''MMM％'';


或


选择*来自t其中new_upper_f喜欢上层（''mmm％''）;


John


---------------------------（广播结束）-------- -------------------

提示8：解释分析是你的朋友

Reading the archives and the FAQ, it seems to be implied that LIKE can
use index (and ILIKE can''t; so to do case-insensitive search you need to
create a functional index on LOWER(field) and say: LOWER(field) LIKE
''foo%'').

However, EXPLAIN always says seq scan for the test data I''m using. I''ve
done ''set enable_seqscan to off'' and it still says seq scan. I was
curious as to how the index will help this query:

db1=> set enable_seqscan to off;
SET
Time: 5.732 ms
db1=> explain select * from t where f like ''xx%'';
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on t (cost=100000000.00..100002698.90 rows=89 width=14)
Filter: (f ~~ ''xx%''::text)
(2 rows)

db1=> explain select * from t where lower(f) like ''xx%'';
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on t (cost=100000000.00..100002893.68 rows=390 width=14)
Filter: (lower(f) ~~ ''xx%''::text)
(2 rows)

The table is:

db1=> \d t
Table "public.t"
Column | Type | Modifiers
--------+------+-----------
f | text |
Indexes:
"i1" unique, btree (lower(f))
"i2" unique, btree (f)

It contains +- 250k rows of totally random 10-char-long strings
(containing upper- & lowercase letters and numbers). Here''s how the LIKE
performs:

db1=> select * from t where f like ''xx%'';
f
------------
xxEqfLZMkH
xxBRRnLetJ
...
xxFPYJEiYf
(98 rows)

Time: 452.613 ms

Would using an index potentially help the performance of this query, and
if yes, how do I force Postgres to use the index?

db1=> select * from t where lower(f) like ''mmm%'';
f
------------
MmmyEVmfSY
MMmzolhHtq
...
mMMWEQzlKm
(16 rows)

Time: 634.470 ms

--
dave
---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster

David Garamond said:

Would using an index potentially help the performance of this query, and
if yes, how do I force Postgres to use the index?

db1=> select * from t where lower(f) like ''mmm%'';

I suspect the fact that you''re specifying the lower function on the column
data, ie lower(f), implies that the function has to be applied to every
row in the table in order to calculate the value prior to testing the like
condition.

I don''t know enough about what you can and cannot do index-wise in PG, in
terms of creating an index based on a computed (upper/lower) value of a
column.

But you could consider adding an extra column to the table and a trigger
so that the trigger places an UPPER or LOWER version of the column "f"
into the new column.

Like searches would then be

select * from t where new_upper_f like upper(''MMM%'');

Provided that there is an index on the new column, new_upper_f, you should
avoid the full table scan. (I think, I haven''t tested this out)...

John Sidney-Woollett
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

try this:
CREATE [ UNIQUE ] INDEX my_index ON t ( lower(f));

John Sidney-Woollett wrote:

David Garamond said:

Would using an index potentially help the performance of this query, and
if yes, how do I force Postgres to use the index?

db1=> select * from t where lower(f) like ''mmm%'';

I suspect the fact that you''re specifying the lower function on the column
data, ie lower(f), implies that the function has to be applied to every
row in the table in order to calculate the value prior to testing the like
condition.

I don''t know enough about what you can and cannot do index-wise in PG, in
terms of creating an index based on a computed (upper/lower) value of a
column.

But you could consider adding an extra column to the table and a trigger
so that the trigger places an UPPER or LOWER version of the column "f"
into the new column.

Like searches would then be

select * from t where new_upper_f like upper(''MMM%'');

Provided that there is an index on the new column, new_upper_f, you should
avoid the full table scan. (I think, I haven''t tested this out)...

John Sidney-Woollett
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

John Sidney-Woollett said:

select * from t where new_upper_f like upper(''MMM%'');

I think I meant

select * from t where new_upper_f like ''MMM%'';

or

select * from t where new_upper_f like upper(''mmm%'');

John

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend