From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Steben <mark.steben@drivedominion.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: using regular expression queries with regular expression indexes
Date: Thu, 25 Jun 2020 12:57:49 -0400
Message-ID: <2277795.1593104269@sss.pgh.pa.us> (raw)
In-Reply-To: <CADyzmyykegZ3ZSVm9hYYKirJYGUYYAx_cK1VQYPCz1q+EpQ89g@mail.gmail.com>
References: <CADyzmyykegZ3ZSVm9hYYKirJYGUYYAx_cK1VQYPCz1q+EpQ89g@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, mark.steben@drivedominion.com
Subject: Re: using regular expression queries with regular expression indexes
In-Reply-To: <2277795.1593104269@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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