Hi.
Ok, ok thanks.
My problem is to shorten time of searching full text stored in text field.
The table definition is like following:
CREATE TABLE xxx
(
...
title character varying,
...
fts1body text,
...
)
If user requests keywords, we use a kind of stristr that is targeting
Japanese text encoded in UTF-8.
aaa bbb ccc [Click here to search!]
SELECT * FROM xxx
WHERE TRUE
AND (ddstrike(title,'aaa') OR ddstrike(fts1body,'aaa') OR ...)
AND (ddstrike(title,'bbb') OR ddstrike(fts1body,'bbb') OR ...)
AND ...
As you can imagine easily, yes, it is very slow!
So I need trial and error for speeding up.
My trial is Insert a light weight filter done by integer key, before text
searching!
For example,
filter('A') - 1
filter('B') - 2
filter('C') - 4
filter('AAABBC') - 7 or {1,2,4}
It may fit to inverse index like GIN!
So I began to study GIN.
I'm sorry to say. Today I found I could apply int array GIN support at
contrib/_int.sql.
I made GIN index.
CREATE INDEX xxx_idx_filter ON xxx USING GIN (filter(fts1body) gist__int_ops);
The following sample query is very very fast! 11065 hits in 22 milli secs
(total 215,278 records).
SELECT COUNT(*) FROM xxx WHERE filter(fts1body) @ filter('ABC');
However the following query is very slow! 9,400ms. It uses Seq Scan lol.
SELECT * FROM xxx
WHERE TRUE
AND (ddstrike(title,'ABC') OR (filter(fts1body) @ filter('AAA') AND
ddstrike(fts1body,'AAA')))
Apply filter to title column too.
The best query result costs 3,700ms. 18 hits. It surely uses expected query
plan: two Bitmap index scan - Bitmap Or - Bitmap Heap Scan.
SELECT * FROM xxx
WHERE TRUE
AND (filter(title) @ filter('ABC') OR filter(fts1body) @ filter('ABC'))
AND (ddstrike(title,'ABC') OR ddstrike(fts1body,'ABC'))
The pure query costs 3,800ms. 18 hits. Single Seq Scan.
SELECT * FROM xxx
WHERE TRUE
AND (ddstrike(title,'ABC') OR ddstrike(fts1body,'ABC'))
Finally I noticed I had spent useless time, and need to find another good one.
Sorry.
However, I may think good idea which uses inverted index.
So I want to know...
- the actual work of extractQuery and consistant.
- the detail interface of extractValue/extractQuery/consistant. It may help
understanding.
I looked at contrib/_int.sql of PG8.2.22
There are definitions of int[] GIN support.
---
CREATE OPERATOR CLASS gin__int_ops
FOR TYPE _int4 USING gin
AS
OPERATOR3 ,
OPERATOR6 = (anyarray, anyarray) RECHECK,
OPERATOR7 @,
OPERATOR8 @ RECHECK,
OPERATOR13 @,
OPERATOR14 ~ RECHECK,
OPERATOR20 @@ (_int4, query_int),
FUNCTION1 btint4cmp (int4, int4),
FUNCTION2 ginarrayextract (anyarray, internal),
FUNCTION3 ginint4_queryextract (internal, internal, int2),
FUNCTION4 ginint4_consistent (internal, int2, internal),
STORAGE int4;
---
I checked the PG8.2.22 source code.
Both ginint4_queryextract and ginint4_consistent assume that query argument
is a PGARRAY (ArrayType *). Where is it decided? Is it array of STORAGE type?
Both extractQuery(ginint4_queryextract) and extractValue(ginarrayextract) seem
to return similar value type. They return Datum array of int4. Is it array of
STORAGE type?
I want to understand the overview of GIN extension.
Thanks
kenji uno
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
