Re: using regular expression queries with regular expression indexes

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: using regular expression queries with regular expression indexes
Дата
Msg-id 2277795.1593104269@sss.pgh.pa.us
обсуждение исходный текст
Ответ на using regular expression queries with regular expression indexes  (Mark Steben <mark.steben@drivedominion.com>)
Список pgsql-admin
Mark Steben <mark.steben@drivedominion.com> writes:
> We are currently running postgres version 12.2. I have a query that uses
> regexp code to confirm if a text field is a date or not.  It is resource
> intensive and long running.  I have made a regexp index (actually several
> flavors of regexp indexes) to aid in this queries performance. However I
> cannot seem to 'convince' the planner to use the indexes I create.

I don't understand your complaint.  Your example shows that it *is*
using the index:

>    ->  Bitmap Index Scan on custattr_value  (cost=0.00..9787.64 rows=479041 width=0)

> Finally if I remove the 'ca.account_id is not null' expression
> [ it doesn't use the index ]

Well, of course not, because then the query doesn't match the index's
WHERE condition (i.e., there could be rows the query needs to return that
are not included in the index).  But I imagine that restriction isn't very
selective, so you could probably omit it from the index definition as
well; then the index would work for queries with or without that
restriction.

            regards, tom lane



В списке pgsql-admin по дате отправления:

Предыдущее
От: Mark Steben
Дата:
Сообщение: using regular expression queries with regular expression indexes
Следующее
От: Thomas Kellerer
Дата:
Сообщение: Re: using regular expression queries with regular expression indexes