Hi,
I've done a fair amount of testing by loading pgsql-general archives
into a database and running a bunch of simple ts queries that use a GIN
index.
I've tested this as well as the two other patches, but as I was able to
get meaningful results only from this patch, I'll post the results here
and info about segfaults and other observed errors to the other threads.
First of all - update the commitfest page whenever you submit a new
patch version, please. I've spent two or three hours testing and
debugging a patches linked from those pages only to find out that there
are newer versions. I should have checked that initially, but let's keep
that updated.
I wan't able to apply the patches to the current head, so I've used
b8fd1a09 (from 17/06) as a base commit.
The following table shows these metrics:
* data load - how long it took to import ~200k messages from the list archive - includes a lot of time spent in
Python(parsing), checking FKs ... - so unless this is significantly higher, it's probably OK
* index size - size of the main GIN index on message body
* 1/2/3-word(s) - number of queries in the form
SELECT id FROM messages WHERE body_tsvector @@ plainto_tsquery('english', 'w1 w2') LIMIT 100
(executed over 60 seconds, and 'per second' speed)
All the scripts are available at https://bitbucket.org/tvondra/archie
Now, the results:
no patches: data load: 710 s index size: 545 MB 1 word: 37500 (630/s) 2 words: 49800 (800/s) 3
words: 40000 (660/s)
additional info (ginaddinfo.7.patch): data load: 693 s index size: 448 MB 1 word: 135000 (2250/s) 2 words:
85000 (1430/s) 3 words: 54000 ( 900/s)
additional info + fast scan (gin_fast_scan.4.patch): data load: 720 s index size: 455 MB 1 word: FAIL 2
words: FAIL 3 words: FAIL
additional info + fast scan + ordering (gin_ordering.4.patch): data load: FAIL index size: N/A 1 word: N/A
2words: N/A 3 words: N/A
So the speedup after adding info into GIN seems very promising, although
I don't quite understand why searching for two words is so much slower.
Also the index size seems to decrease significantly.
After applying 'fast scan' the things started to break down, so I wasn't
able to run the queries and then even the load failed consistently.
I'll post the info into the appropriate threads.
Tomas