Message-ID: <52E9A274.6040806@fuzzy.cz>
Date: Thu, 30 Jan 2014 01:53:08 +0100
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.1.1
MIME-Version: 1.0
To: pgsql-hackers@postgresql.org
Subject: Re: GIN improvements part2: fast scan
References: 
 <CAPpHfdvftaJq7www381naLw1=4u0h+qpXgWvNhcEB9HMVywbGg@mail.gmail.com>
 <51C4ACC5.2090707@vmware.com>
 <CAPpHfduJ-X9WKR9h-vyTrNEVNR37ZDx05qOLkA3ys3j-14uKXA@mail.gmail.com>
 <CAPpHfduRrMOvXNrmJ0+ALk9dDR3Z34HpNdQPkQZtF_oYX58h9A@mail.gmail.com>
 <51D00FB6.9030008@vmware.com>
 <CAPpHfdtsDe-O6oQdCZUh6N1gGOMz0XKTMNQro-fueg-R06PaWw@mail.gmail.com>
 <528533D9.3040501@vmware.com>
 <CAPpHfduOvUFT+GgCrnBOzRR19eTb9mDsDJwU=S0h477fdgr=RA@mail.gmail.com>
 <CAPpHfdtZOhsmz_uf5BfdXcj5W3zk3K-PYxFc8-7eQw-nPXX1DA@mail.gmail.com>
 <CAPpHfdsEKFmrOU51EWSbF-RDESasSVa2YjoyFoG+0jCZOn1qjw@mail.gmail.com>
 <CAPpHfdvs-EHiwmTgfK=uJ4W3OjskVHyfkuehtyrKfKY_KmMpVw@mail.gmail.com>
 <52E141BA.5000302@vmware.com> <52E4141E.60609@fuzzy.cz>
 <52E42AD0.5060600@vmware.com> <52E4AA3A.9000607@fuzzy.cz>
 <52E53458.2010106@vmware.com>
 <CAPpHfdticvY2+RGTxzQdrr4YSQie+vYGRDRBTuG1ZxBmjwGrOA@mail.gmail.com>
 <CAPpHfduivz3MOA8_o+44n_Z0P-n+WCW=K1DrH+-g3gb-cbM2aA@mail.gmail.com>
 <52E72A10.5040908@fuzzy.cz> <52E75C54.9050209@vmware.c om>
In-Reply-To: <52E75C54.9050209@vmware.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 28.1.2014 08:29, Heikki Linnakangas wrote:
> On 01/28/2014 05:54 AM, Tomas Vondra wrote:
>> Then I ran those scripts on:
>>
>>    * 9.3
>>    * 9.4 with Heikki's patches (9.4-heikki)
>>    * 9.4 with Heikki's and first patch (9.4-alex-1)
>>    * 9.4 with Heikki's and both patches (9.4-alex-2)
> 
> It would be good to also test with unpatched 9.4 (ie. git master). The
> packed posting lists patch might account for a large part of the
> differences between 9.3 and the patched 9.4 versions.
> 
> - Heikki
> 

Hi,

the e-mail I sent yesterday apparently did not make it into the list,
probably because of the attachments, so I'll just link them this time.

I added the results from 9.4 master to the spreadsheet:

https://docs.google.com/spreadsheet/ccc?key=0Alm8ruV3ChcgdHJfZTdOY2JBSlkwZjNuWGlIaGM0REE

It's a bit cumbersome to analyze though, so I've quickly hacked up a
simple jqplot page that allows comparing the results. It's available
here: http://www.fuzzy.cz/tmp/gin/

It's likely there are some quirks and issues - let me know about them.

The ODT with the data is available here:

       http://www.fuzzy.cz/tmp/gin/gin-scan-benchmarks.ods


Three quick basic observations:

(1) The current 9.4 master is consistently better than 9.3 by about 15%
    on rare words, and up to 30% on common words. See the charts for
    6-word queries:

       http://www.fuzzy.cz/tmp/gin/6-words-rare-94-vs-93.png
       http://www.fuzzy.cz/tmp/gin/6-words-rare-94-vs-93.png

    With 3-word queries the effects are even stronger & clearer,
    especially with the common words.

(2) Heikki's patches seem to work OK, i.e. improve the performance, but
    only with rare words.

      http://www.fuzzy.cz/tmp/gin/heikki-vs-94-rare.png

    With 3 words the impact is much stronger than with 6 words,
    presumably because it depends on how frequent the combination of
    words is (~ multiplication of probabilities). See

      http://www.fuzzy.cz/tmp/gin/heikki-vs-94-3-common-words.png
      http://www.fuzzy.cz/tmp/gin/heikki-vs-94-6-common-words.png

    for comparison of 9.4 master vs. 9.4+heikki's patches.

(3) A file with explain plans for 4 queries suffering ~2x slowdown,
    and explain plans with 9.4 master and Heikki's patches is available
    here:

      http://www.fuzzy.cz/tmp/gin/queries.txt

    All the queries have 6 common words, and the explain plans look
    just fine to me - exactly like the plans for other queries.

    Two things now caught my eye. First some of these queries actually
    have words repeated - either exactly like "database & database" or
    in negated form like "!anything & anything". Second, while
    generating the queries, I use "dumb" frequency, where only exact
    matches count. I.e. "write != written" etc. But the actual number
    of hits may be much higher - for example "write" matches exactly
    just 5% documents, but using @@ it matches more than 20%.

    I don't know if that's the actual cause though.

regards
Tomas


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers