Horton

Re: WIP: index support for regexp search

Alexander Korotkov <aekorotkov@gmail.com>

View thread
From: Alexander Korotkov <aekorotkov@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Erikjan Rijkers <er@xs4all.nl>, Heikki Linnakangas <hlinnakangas@vmware.com>, Tomas Vondra <tv@fuzzy.cz>, pgsql-hackers <pgsql-hackers@postgresql.org>, Pavel Stìhule <pavel.stehule@gmail.com>
Date: 2013-04-15T13:53:41Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix filling of postmaster.pid in bootstrap/standalone mode.

  2. Add explicit casts in ilist.h's inline functions.

Attachments

I found you committed GiST index implementation. That's cool.
I found an easy way to optimize it. We can also use trigramsMatchGraph for
signatures. Attached patch contains implementation.
Simple example in order to demonstrate it:

Before the patch:

test=# explain (analyze, buffers) select * from words where s ~ '[abc]def';
                                                        QUERY PLAN


--------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on words  (cost=4.36..40.24 rows=10 width=9) (actual
time=17.189..17.193 rows=3 loops=1)
   Recheck Cond: (s ~ '[abc]def'::text)
   Buffers: shared hit=858
   ->  Bitmap Index Scan on words_trgm_idx  (cost=0.00..4.36 rows=10
width=0) (actual time=17.172..17.172 rows=3 loops=1)
         Index Cond: (s ~ '[abc]def'::text)
         Buffers: shared hit=*857*
 Total runtime: 17.224 ms
(7 rows)

After the patch:

test=# explain (analyze, buffers) select * from words where s ~ '[abc]def';
                                                        QUERY PLAN


--------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on words  (cost=4.36..40.24 rows=10 width=9) (actual
time=13.718..13.721 rows=3 loops=1)
   Recheck Cond: (s ~ '[abc]def'::text)
   Buffers: shared hit=498
   ->  Bitmap Index Scan on words_trgm_idx  (cost=0.00..4.36 rows=10
width=0) (actual time=13.701..13.701 rows=3 loops=1)
         Index Cond: (s ~ '[abc]def'::text)
         Buffers: shared hit=*497*
 Total runtime: 13.786 ms
(7 rows)


------
With best regards,
Alexander Korotkov.