On Sat, January 21, 2012 06:26, Alexander Korotkov wrote:
> Hi!
>
> Thank you for your feedback!
>
> On Fri, Jan 20, 2012 at 3:33 AM, Erik Rijkers <e...@xs4all.nl> wrote:
>
>> The patch yields spectacular speedups with small, simple-enough regexen.
>> But it does not do a
>> good enough job when guessing where to use the index and where fall back
>> to Seq Scan. This can
>> lead to (also spectacular) slow-downs, compared to Seq Scan.
>>
> Could you give some examples of regexes where index scan becomes slower
> than seq scan?
>
x[aeio]+q takes many minutes, uninterruptible. It's now running for almost 30
minutes, I'm sure
it will come back eventually, but I think I'll kill it later on; I suppose you
get the point ;-)
Even with {n,m} quantifiers it's easy to hit slowdowns:
(table azjunk6 is 112 MB, the index 693 MB.)
(MAX_COLOR_CHARS=4 <= your original patch)
instance table regex plan time
HEAD azjunk6 x[aeio]{1,3}q Seq Scan 3566.088
ms
HEAD azjunk6 x[aeio]{1,3}q Seq Scan 3540.606
ms
HEAD azjunk6 x[aeio]{1,3}q Seq Scan 3495.034
ms
HEAD azjunk6 x[aeio]{1,3}q Seq Scan 3510.403
ms
trgm_regex azjunk6 x[aeio]{1,3}q Bitmap Heap Scan 3724.131
ms
trgm_regex azjunk6 x[aeio]{1,3}q Bitmap Heap Scan 3844.999
ms
trgm_regex azjunk6 x[aeio]{1,3}q Bitmap Heap Scan 3835.190
ms
trgm_regex azjunk6 x[aeio]{1,3}q Bitmap Heap Scan 3724.016
ms
HEAD azjunk6 x[aeio]{1,4}q Seq Scan 3617.997
ms
HEAD azjunk6 x[aeio]{1,4}q Seq Scan 3644.215
ms
HEAD azjunk6 x[aeio]{1,4}q Seq Scan 3636.976
ms
HEAD azjunk6 x[aeio]{1,4}q Seq Scan 3625.493
ms
trgm_regex azjunk6 x[aeio]{1,4}q Bitmap Heap Scan 7885.247
ms
trgm_regex azjunk6 x[aeio]{1,4}q Bitmap Heap Scan 8799.082
ms
trgm_regex azjunk6 x[aeio]{1,4}q Bitmap Heap Scan 7754.152
ms
trgm_regex azjunk6 x[aeio]{1,4}q Bitmap Heap Scan 7721.332
ms
This is with your patch as is; in instances compiled with higher
MAX_COLOR_CHARS (I did 6 and 9),
it is of course even easier to dream up a slow regex...
Erik Rijkers
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
