From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Mark Steben <mark(dot)steben(at)drivedominion(dot)com> |
Cc: | pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: using regular expression queries with regular expression indexes |
Date: | 2020-06-25 16:57:49 |
Message-ID: | 2277795.1593104269@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Mark Steben <mark(dot)steben(at)drivedominion(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2020-06-25 18:17:47 | Re: using regular expression queries with regular expression indexes |
Previous Message | Mark Steben | 2020-06-25 16:45:55 | using regular expression queries with regular expression indexes |