pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedis it possible to get the optimizer to use indexes with a like clause
11+ messages / 7 participants
[nested] [flat]
* is it possible to get the optimizer to use indexes with a like clause
@ 2003-12-18 23:18 Dave Cramer <pg@fastcrypt.com>
2003-12-19 01:38 ` Re: is it possible to get the optimizer to use indexes Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-12-19 03:22 ` Re: is it possible to get the optimizer to use indexes with a like clause Christopher Browne <cbbrowne@acm.org>
0 siblings, 2 replies; 11+ messages in thread
From: Dave Cramer @ 2003-12-18 23:18 UTC (permalink / raw)
To: pgsql-performance
It appears that the optimizer only uses indexes for = clause?
Dave
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: is it possible to get the optimizer to use indexes
2003-12-18 23:18 is it possible to get the optimizer to use indexes with a like clause Dave Cramer <pg@fastcrypt.com>
@ 2003-12-19 01:38 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-12-19 01:38 ` Re: is it possible to get the optimizer to use indexes Doug McNaught <doug@mcnaught.org>
2003-12-19 03:08 ` Re: is it possible to get the optimizer to use indexes Dave Cramer <pg@fastcrypt.com>
1 sibling, 2 replies; 11+ messages in thread
From: Christopher Kings-Lynne @ 2003-12-19 01:38 UTC (permalink / raw)
To: pg@fastcrypt.com; +Cc: pgsql-performance
> It appears that the optimizer only uses indexes for = clause?
The optimizer will used indexes for LIKE clauses, so long as the clause
is a prefix search, eg:
SELECT * FROM test WHERE a LIKE 'prf%';
Chris
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: is it possible to get the optimizer to use indexes
2003-12-18 23:18 is it possible to get the optimizer to use indexes with a like clause Dave Cramer <pg@fastcrypt.com>
2003-12-19 01:38 ` Re: is it possible to get the optimizer to use indexes Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2003-12-19 01:38 ` Doug McNaught <doug@mcnaught.org>
2003-12-24 13:58 ` Re: is it possible to get the optimizer to use indexes Dave Cramer <pg@fastcrypt.com>
1 sibling, 1 reply; 11+ messages in thread
From: Doug McNaught @ 2003-12-19 01:38 UTC (permalink / raw)
To: pgsql-performance
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> It appears that the optimizer only uses indexes for = clause?
>
> The optimizer will used indexes for LIKE clauses, so long as the
> clause is a prefix search, eg:
>
> SELECT * FROM test WHERE a LIKE 'prf%';
Doesn't this still depend on your locale?
-Doug
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: is it possible to get the optimizer to use indexes
2003-12-18 23:18 is it possible to get the optimizer to use indexes with a like clause Dave Cramer <pg@fastcrypt.com>
2003-12-19 01:38 ` Re: is it possible to get the optimizer to use indexes Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-12-19 01:38 ` Re: is it possible to get the optimizer to use indexes Doug McNaught <doug@mcnaught.org>
@ 2003-12-24 13:58 ` Dave Cramer <pg@fastcrypt.com>
0 siblings, 0 replies; 11+ messages in thread
From: Dave Cramer @ 2003-12-24 13:58 UTC (permalink / raw)
To: Doug McNaught <doug@mcnaught.org>; +Cc: pgsql-performance
Doug,
Yes, it does depend on the locale, you can get around this in 7.4 by
building the index with smart operators
Dave
On Thu, 2003-12-18 at 20:38, Doug McNaught wrote:
> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>
> >> It appears that the optimizer only uses indexes for = clause?
> >
> > The optimizer will used indexes for LIKE clauses, so long as the
> > clause is a prefix search, eg:
> >
> > SELECT * FROM test WHERE a LIKE 'prf%';
>
> Doesn't this still depend on your locale?
>
> -Doug
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: is it possible to get the optimizer to use indexes
2003-12-18 23:18 is it possible to get the optimizer to use indexes with a like clause Dave Cramer <pg@fastcrypt.com>
2003-12-19 01:38 ` Re: is it possible to get the optimizer to use indexes Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2003-12-19 03:08 ` Dave Cramer <pg@fastcrypt.com>
2003-12-19 03:36 ` Re: is it possible to get the optimizer to use indexes Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-12-19 03:44 ` Re: is it possible to get the optimizer to use indexes Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 11+ messages in thread
From: Dave Cramer @ 2003-12-19 03:08 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: pgsql-performance
after vacuum verbose analyze, I still get
explain select * from isppm where item_upc_cd like '06038301234';
QUERY PLAN
-----------------------------------------------------------------------
Seq Scan on isppm (cost=100000000.00..100009684.89 rows=2 width=791)
Filter: (item_upc_cd ~~ '06038301234'::text)
(2 rows)
isp=# explain select * from isppm where item_upc_cd = '06038301234';
QUERY PLAN
------------------------------------------------------------------------
Index Scan using isppm_x0 on isppm (cost=0.00..5.86 rows=2 width=791)
Index Cond: (item_upc_cd = '06038301234'::bpchar)
(2 rows)
Dave
On Thu, 2003-12-18 at 20:38, Christopher Kings-Lynne wrote:
> > It appears that the optimizer only uses indexes for = clause?
>
> The optimizer will used indexes for LIKE clauses, so long as the clause
> is a prefix search, eg:
>
> SELECT * FROM test WHERE a LIKE 'prf%';
>
> Chris
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: is it possible to get the optimizer to use indexes
2003-12-18 23:18 is it possible to get the optimizer to use indexes with a like clause Dave Cramer <pg@fastcrypt.com>
2003-12-19 01:38 ` Re: is it possible to get the optimizer to use indexes Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-12-19 03:08 ` Re: is it possible to get the optimizer to use indexes Dave Cramer <pg@fastcrypt.com>
@ 2003-12-19 03:36 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 11+ messages in thread
From: Stephan Szabo @ 2003-12-19 03:36 UTC (permalink / raw)
To: Dave Cramer <pg@fastcrypt.com>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; pgsql-performance
On Thu, 18 Dec 2003, Dave Cramer wrote:
> after vacuum verbose analyze, I still get
>
> explain select * from isppm where item_upc_cd like '06038301234';
> QUERY PLAN
> -----------------------------------------------------------------------
> Seq Scan on isppm (cost=100000000.00..100009684.89 rows=2 width=791)
> Filter: (item_upc_cd ~~ '06038301234'::text)
> (2 rows)
IIRC, the other limitation is that it only does so in "C" locale due to
wierdnesses in other locales.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: is it possible to get the optimizer to use indexes
2003-12-18 23:18 is it possible to get the optimizer to use indexes with a like clause Dave Cramer <pg@fastcrypt.com>
2003-12-19 01:38 ` Re: is it possible to get the optimizer to use indexes Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-12-19 03:08 ` Re: is it possible to get the optimizer to use indexes Dave Cramer <pg@fastcrypt.com>
@ 2003-12-19 03:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-12-19 09:50 ` Re: is it possible to get the optimizer to use indexes Dave Cramer <pg@fastcrypt.com>
1 sibling, 1 reply; 11+ messages in thread
From: Tom Lane @ 2003-12-19 03:44 UTC (permalink / raw)
To: pg@fastcrypt.com; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; pgsql-performance
Dave Cramer <pg@fastcrypt.com> writes:
> after vacuum verbose analyze, I still get [a seqscan]
The other gating factor is that you have to have initdb'd in C locale.
Non-C locales tend to use wild and wooly sort orders that are not
compatible with what LIKE needs.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: is it possible to get the optimizer to use indexes
2003-12-18 23:18 is it possible to get the optimizer to use indexes with a like clause Dave Cramer <pg@fastcrypt.com>
2003-12-19 01:38 ` Re: is it possible to get the optimizer to use indexes Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-12-19 03:08 ` Re: is it possible to get the optimizer to use indexes Dave Cramer <pg@fastcrypt.com>
2003-12-19 03:44 ` Re: is it possible to get the optimizer to use indexes Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-12-19 09:50 ` Dave Cramer <pg@fastcrypt.com>
2003-12-19 14:38 ` Re: is it possible to get the optimizer to use indexes Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Dave Cramer @ 2003-12-19 09:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; pgsql-performance
So even in a north-american locale, such as en_CA this will be a
problem?
Dave
On Thu, 2003-12-18 at 22:44, Tom Lane wrote:
> Dave Cramer <pg@fastcrypt.com> writes:
> > after vacuum verbose analyze, I still get [a seqscan]
>
> The other gating factor is that you have to have initdb'd in C locale.
> Non-C locales tend to use wild and wooly sort orders that are not
> compatible with what LIKE needs.
>
> regards, tom lane
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: is it possible to get the optimizer to use indexes
2003-12-18 23:18 is it possible to get the optimizer to use indexes with a like clause Dave Cramer <pg@fastcrypt.com>
2003-12-19 01:38 ` Re: is it possible to get the optimizer to use indexes Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-12-19 03:08 ` Re: is it possible to get the optimizer to use indexes Dave Cramer <pg@fastcrypt.com>
2003-12-19 03:44 ` Re: is it possible to get the optimizer to use indexes Tom Lane <tgl@sss.pgh.pa.us>
2003-12-19 09:50 ` Re: is it possible to get the optimizer to use indexes Dave Cramer <pg@fastcrypt.com>
@ 2003-12-19 14:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-12-19 16:00 ` Re: is it possible to get the optimizer to use indexes Erki Kaldjärv <erki@webware.ee>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2003-12-19 14:38 UTC (permalink / raw)
To: pg@fastcrypt.com; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; pgsql-performance
Dave Cramer <pg@fastcrypt.com> writes:
> So even in a north-american locale, such as en_CA this will be a
> problem?
If it's not "C" we won't try to optimize LIKE. I know en_US does not
work (case-insensitive, funny rules about spaces, etc) and I would
expect en_CA has the same issues.
If you're using 7.4 you have the option to create a special index
instead of re-initdb'ing your whole database.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: is it possible to get the optimizer to use indexes
2003-12-18 23:18 is it possible to get the optimizer to use indexes with a like clause Dave Cramer <pg@fastcrypt.com>
2003-12-19 01:38 ` Re: is it possible to get the optimizer to use indexes Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-12-19 03:08 ` Re: is it possible to get the optimizer to use indexes Dave Cramer <pg@fastcrypt.com>
2003-12-19 03:44 ` Re: is it possible to get the optimizer to use indexes Tom Lane <tgl@sss.pgh.pa.us>
2003-12-19 09:50 ` Re: is it possible to get the optimizer to use indexes Dave Cramer <pg@fastcrypt.com>
2003-12-19 14:38 ` Re: is it possible to get the optimizer to use indexes Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-12-19 16:00 ` Erki Kaldjärv <erki@webware.ee>
0 siblings, 0 replies; 11+ messages in thread
From: Erki Kaldjärv @ 2003-12-19 16:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pg@fastcrypt.com, Christopher Kings-Lynne <chriskl@familyhealth.com.au>; pgsql-performance
Hello,
i got indexes to work with "text_pattern_ops" for locale et_EE.
So instead of:
create index some_index_name on some_table(some_text_field);
nor
create index some_index_name on some_table(some_text_field text_ops);
try to create index as follows:
create index some_index_name on some_table(some_text_field
text_pattern_ops);
Note that text_pattern_ops is available pg >= 7.4.
Regards,
Erki Kaldjärv
Webware OÜ
www.webware.ee
Tom Lane wrote:
>Dave Cramer <pg@fastcrypt.com> writes:
>
>
>>So even in a north-american locale, such as en_CA this will be a
>>problem?
>>
>>
>
>If it's not "C" we won't try to optimize LIKE. I know en_US does not
>work (case-insensitive, funny rules about spaces, etc) and I would
>expect en_CA has the same issues.
>
>If you're using 7.4 you have the option to create a special index
>instead of re-initdb'ing your whole database.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: is it possible to get the optimizer to use indexes with a like clause
2003-12-18 23:18 is it possible to get the optimizer to use indexes with a like clause Dave Cramer <pg@fastcrypt.com>
@ 2003-12-19 03:22 ` Christopher Browne <cbbrowne@acm.org>
1 sibling, 0 replies; 11+ messages in thread
From: Christopher Browne @ 2003-12-19 03:22 UTC (permalink / raw)
To: pgsql-performance
pg@fastcrypt.com (Dave Cramer) wrote:
> It appears that the optimizer only uses indexes for = clause?
It can use indices only if there is a given prefix.
Thus:
where text_field like 'A%'
can use the index, essentially transforming this into the clauses
where text_field >= 'A' and
text_field < 'B'.
You can't get much out of an index for
where text_field like '%SOMETHING'
--
(reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc"))
http://www3.sympatico.ca/cbbrowne/wp.html
"When the grammar checker identifies an error, it suggests a
correction and can even makes some changes for you."
-- Microsoft Word for Windows 2.0 User's Guide, p.35:
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2003-12-24 13:58 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-18 23:18 is it possible to get the optimizer to use indexes with a like clause Dave Cramer <pg@fastcrypt.com>
2003-12-19 01:38 ` Re: is it possible to get the optimizer to use indexes Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-12-19 01:38 ` Re: is it possible to get the optimizer to use indexes Doug McNaught <doug@mcnaught.org>
2003-12-24 13:58 ` Re: is it possible to get the optimizer to use indexes Dave Cramer <pg@fastcrypt.com>
2003-12-19 03:08 ` Re: is it possible to get the optimizer to use indexes Dave Cramer <pg@fastcrypt.com>
2003-12-19 03:36 ` Re: is it possible to get the optimizer to use indexes Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-12-19 03:44 ` Re: is it possible to get the optimizer to use indexes Tom Lane <tgl@sss.pgh.pa.us>
2003-12-19 09:50 ` Re: is it possible to get the optimizer to use indexes Dave Cramer <pg@fastcrypt.com>
2003-12-19 14:38 ` Re: is it possible to get the optimizer to use indexes Tom Lane <tgl@sss.pgh.pa.us>
2003-12-19 16:00 ` Re: is it possible to get the optimizer to use indexes Erki Kaldjärv <erki@webware.ee>
2003-12-19 03:22 ` Christopher Browne <cbbrowne@acm.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox