Today we have two interesting patches:
- patch by Teodor Sigaev and Oleg Bartunov, and committed by Tom Lane, which adds interesting capability to GIN indexes
- patch by Zoltan Boszormenyi, also committed by Tom, which adds “RESTART" option to ALTER SEQUENCE. With some interesting consequences
Since describing patch on GIN will take much more time and blog-post-space, I'll first go into details of SEQUENCE RESTART patch.
Basically, newly added syntax let's you do something like this:
# CREATE SEQUENCE test; CREATE SEQUENCE # SELECT NEXTVAL('test'); NEXTVAL --------- 1 (1 ROW) # SELECT COUNT(NEXTVAL('test')) FROM generate_series(1,10000); COUNT ------- 10000 (1 ROW) # SELECT NEXTVAL('test'); NEXTVAL --------- 10002 (1 ROW) # ALTER SEQUENCE test restart; ALTER SEQUENCE # SELECT NEXTVAL('test'); NEXTVAL --------- 1 (1 ROW)
It doesn't seem very revolutionary – after all you could always do:
SELECT SETVAL('test', 1);
But you have to remember that not all sequences start from 0.
What's more – having this syntax (or, to be more specific, this ability) to restart sequence, gave us something else:
# CREATE TABLE test (id serial PRIMARY KEY, something int4); NOTICE: CREATE TABLE will CREATE implicit SEQUENCE "test_id_seq" FOR serial COLUMN "test.id" NOTICE: CREATE TABLE / PRIMARY KEY will CREATE implicit INDEX "test_pkey" FOR TABLE "test" CREATE TABLE # INSERT INTO test (something) VALUES (15) returning *; id | something ----+----------- 1 | 15 (1 ROW) INSERT 0 1 # INSERT INTO test (something) SELECT * FROM generate_series(1000, 10000, 5); INSERT 0 1801 # TRUNCATE TABLE test RESTART IDENTITY; TRUNCATE TABLE # INSERT INTO test (something) VALUES (50) returning *; id | something ----+----------- 1 | 50 (1 ROW) INSERT 0 1
In case you missed that – now truncate can automatically restart sequence that is used for primary key generator. And this is what I found really cool.
Now to the second (or first, depending how you'll look at it) patch: partial-matches in GIN.
Of course the most prominent use case for GIN is TSearch2. So I'll concentrate on using partial-matches in GIN in TSearch2.
First, I have a test table:
# \d pages
TABLE "public.pages"
COLUMN | TYPE | Modifiers
--------+----------+----------------------------------------------------
id | INTEGER | NOT NULL DEFAULT NEXTVAL('pages_id_seq'::regclass)
url | text | NOT NULL
title | text |
body | text |
ft | tsvector |
Indexes:
"pages_pkey" PRIMARY KEY, btree (id)
"pages_url_key" UNIQUE, btree (url)
"tsearch_test" gin (ft)
Triggers:
tsvectorupdate BEFORE INSERT OR UPDATE ON pages FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger('ft', 'public.polish', 'url', 'title', 'body')Which contains some (but very little) data:
# SELECT COUNT(*), MIN(LENGTH(body)), MAX(LENGTH(body)), SUM(LENGTH(body)) FROM pages; COUNT | MIN | MAX | SUM -------+------+--------+--------- 443 | 1147 | 142286 | 2935151 (1 ROW)
This table contains pages from Polish wikipedia, so my TSearch configuration is also based on Polish language. But it shouldn't matter in this case.
For my test I chosen 2 words: drzwi and drzewa (door and trees).
First, let's check it tsearch can tokenize them properly:
# SELECT to_tsvector('public.polish', 'drzwi drzewa'); to_tsvector ---------------------- 'drzewo':2 'drzwi':1 (1 ROW)
Looks fine for me. Now, let's check how fast I can search for trees with tsearch:
# EXPLAIN analyze SELECT * FROM pages WHERE ft @@ to_tsquery('public.polish', 'drzewa'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ INDEX Scan USING tsearch_test ON pages (cost=0.00..16.31 ROWS=1 width=596) (actual TIME=0.038..0.111 ROWS=17 loops=1) INDEX Cond: (ft @@ '''drzewo'''::tsquery) Total runtime: 0.193 ms (3 ROWS)
Pretty fast. How about doors?
# EXPLAIN analyze SELECT * FROM pages WHERE ft @@ to_tsquery('public.polish', 'drzwi'); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- INDEX Scan USING tsearch_test ON pages (cost=0.00..16.31 ROWS=1 width=596) (actual TIME=0.043..0.046 ROWS=1 loops=1) INDEX Cond: (ft @@ '''drzwi'''::tsquery) Total runtime: 0.099 ms (3 ROWS)
Now, let's check for pages which have any of these two words:
# EXPLAIN analyze SELECT * FROM pages WHERE ft @@ to_tsquery('public.polish', 'drzwi|drzewa'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ INDEX Scan USING tsearch_test ON pages (cost=0.00..16.31 ROWS=1 width=596) (actual TIME=0.057..0.142 ROWS=18 loops=1) INDEX Cond: (ft @@ '''drzwi'' | ''drzewo'''::tsquery) Total runtime: 0.230 ms (3 ROWS)
OK. Looks fine. Times are pretty good. For comparison purposes let's check how “LIKE" will work:
# EXPLAIN analyze SELECT * FROM pages WHERE (title||' '||body) LIKE '%drzwi%'; QUERY PLAN ---------------------------------------------------------------------------------------------------- Seq Scan ON pages (cost=0.00..36.75 ROWS=1 width=596) (actual TIME=24.924..73.689 ROWS=2 loops=1) FILTER: (((title || ' '::text) || body) ~~ '%drzwi%'::text) Total runtime: 73.746 ms (3 ROWS) # EXPLAIN analyze SELECT * FROM pages WHERE (title||' '||body) LIKE '%drzewa%'; QUERY PLAN --------------------------------------------------------------------------------------------------- Seq Scan ON pages (cost=0.00..36.75 ROWS=1 width=596) (actual TIME=9.499..72.118 ROWS=8 loops=1) FILTER: (((title || ' '::text) || body) ~~ '%drzewa%'::text) Total runtime: 72.191 ms (3 ROWS) # EXPLAIN analyze SELECT * FROM pages WHERE (title||' '||body) LIKE '%drz%'; QUERY PLAN ------------------------------------------------------------------------------------------------------ Seq Scan ON pages (cost=0.00..36.75 ROWS=18 width=596) (actual TIME=0.180..63.877 ROWS=108 loops=1) FILTER: (((title || ' '::text) || body) ~~ '%drz%'::text) Total runtime: 64.165 ms (3 ROWS)
As you can see last test of like is not exactly comparable with ‘drzwi|drzewa', as it found also other words – even words where “drz" is only part of word, not necessarily its beginning.
This new patch, lets TSearch (and other applications) to use GIN indexes for prefix searches. TSearch syntax for this is: ‘prefix:*'. So, let's check how well (or how bad) it will work:
# EXPLAIN analyze SELECT * FROM pages WHERE ft @@ to_tsquery('public.polish', 'drz:*'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ INDEX Scan USING tsearch_test ON pages (cost=0.00..16.31 ROWS=1 width=596) (actual TIME=0.068..0.168 ROWS=23 loops=1) INDEX Cond: (ft @@ '''drz'':*'::tsquery) Total runtime: 0.262 ms (3 ROWS)
Whoa. Pretty fast. And it found 5 new pages. I checked them manually, and found out, that the matched tokens were:
- drzeć
- drzewiecki
- drzeżdżon
- drzwiczki
- drzyzga
So – it works. And to give credit to Oleg and Teodor – it works really fast!
Sweeeeet! I have been waiting for this for some time! Thanks Oleg and Teodor!