pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tomas Vondra <tv@fuzzy.cz>
To: pgsql-hackers@postgresql.org
Subject: Re: GIN improvements part2: fast scan
Date: Sun, 26 Jan 2014 19:20:44 +0100
Message-ID: <52E551FC.7080505@fuzzy.cz> (raw)
In-Reply-To: <52E53458.2010106@vmware.com>
References: <CAPpHfdvftaJq7www381naLw1=4u0h+qpXgWvNhcEB9HMVywbGg@mail.gmail.com>
	<51C16235.3080308@vmware.com>
	<CAPpHfdtMPreArtJpJNKuYCiURvt5=G+UJjx5buHVSnM01Cbi8A@mail.gmail.com>
	<51C170A8.9060509@vmware.com>
	<CAPpHfdvOcjQZwBeT8on1yztt9e4VN_7orDXVbtE26rctS-nP_g@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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>

On 26.1.2014 17:14, Heikki Linnakangas wrote:
> 
> I would actually expect it to be fairly effective for that query, so
> that's a bit surprising. I added counters to see where the calls are
> coming from, and it seems that about 80% of the calls are actually
> coming from this little the feature I explained earlier:
> 
>> In addition to that, I'm using the ternary consistent function to check
>> if minItem is a match, even if we haven't loaded all the entries yet.
>> That's less important, but I think for something like "rare1 | (rare2 &
>> frequent)" it might be useful. It would allow us to skip fetching
>> 'frequent', when we already know that 'rare1' matches for the current
>> item. I'm not sure if that's worth the cycles, but it seemed like an
>> obvious thing to do, now that we have the ternary consistent function.
> 
> So, that clearly isn't worth the cycles :-). At least not with an
> expensive consistent function; it might be worthwhile if we pre-build
> the truth-table, or cache the results of the consistent function.
> 
> Attached is a quick patch to remove that, on top of all the other
> patches, if you want to test the effect.

Indeed, the patch significantly improved the performance. The total
runtime is almost exactly the same as on 9.3 (~22 seconds for 1000
queries). The timing chart (patched vs. 9.3) is attached.

A table with number of queries with duration ratio below some threshold
looks like this:

  threshold |  count | percentage
-------------------------------------
   0.5      |      3 |       0.3%
   0.75     |     45 |       4.5%
   0.9      |    224 |      22.4%
   1.0      |    667 |      66.7%
   1.05     |    950 |      95.0%
   1.1      |    992 |      99.2%

A ratio is just a measure of how much time it took compared to 9.3

    ratio = (duration on patched HEAD) / (duration on 9.3)

The table is cumulative, e.g. values in the 0.9 row mean that for 224
queries the duration with the patches was below 90% of the duration on 9.3.

IMHO the table suggests with the last patch we're fine - majority of
queries (~66%) is faster than on 9.3, and the tail is very short. There
are just 2 queries that took more than 15% longer, compared to 9.3. And
we're talking about 20ms vs. 30ms, so chances are this is just a random
noise.

So IMHO we can go ahead, and maybe tune this a bit more in the future.

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

Attachments:


gin_query_durations_fixed.png (image/png, 18.2K) download
  view image

view thread (90+ messages)  latest in thread

Message-ID: <52E551FC.7080505@fuzzy.cz>
Permalink:  ../52E551FC.7080505@fuzzy.cz/
Also on:    postgresql.org/message-id/52E551FC.7080505@fuzzy.cz

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: tv@fuzzy.cz
  Subject: Re: GIN improvements part2: fast scan
  In-Reply-To: <52E551FC.7080505@fuzzy.cz>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox