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

help / color / mirror / Atom feed
From: Tomas Vondra <tv@fuzzy.cz>
To: pgsql-performance@postgresql.org
Subject: Re: Yet another abort-early plan disaster on 9.3
Date: Fri, 10 Oct 2014 20:13:24 +0200
Message-ID: <543821C4.3030301@fuzzy.cz> (raw)
In-Reply-To: <CAFwQ8renpGVZPorJn4j7O7L_dGLaLqsmccR+7EhGxF0fvDgyTg@mail.gmail.com>
References: <541A2335.3060100@agliodbs.com>
	<5429D517.3090900@agliodbs.com>
	<CAMkU=1wCMLWZexqVUPXWFeKmPPUDMv6GEEFUVDQnb7gfW9Wamw@mail.gmail.com>
	<CA+U5nMLj0iyYpOM+uF934-joy+6S64=yR+d6cnp94ZWzk2xkug@mail.gmail.com>
	<WM!ce2c941557a0e9090a3cb66d0469128c782bb4d7b1f60d15a5c91279578d4c6fd237efa5b4660e94a0056752c695113e!@asav-2.01.com>
	<542C4E60.3030505@agliodbs.com>
	<CA+U5nMKqqPNpSr2CQ2V0=9bjWVv-OA28o3BoxYP=7YVfyswdzw@mail.gmail.com>
	<CAEYLb_UTz=dtxYOCYF_D1s7wQJfoZUrey6Fjxq08VFueXWoEKQ@mail.gmail.com>
	<WM!341e0135c2f371f48830484cdb3da37506d776453b10db7a9af11d648214c4b437402f04e7ccb3d30918cbc64dfee95d!@asav-1.01.com>
	<542DADEB.2080103@agliodbs.com>
	<CAM-w4HNfZymQmTu3+TxQQD-e6_410-sDnZBaW8neurmTFh4GbA@mail.gmail.com>
	<f4c06746b31e468f9550fb9924798da3.squirrel@2.emaily.eu>
	<CAFwQ8rd0C47ZkCGONPa1UfJg2nJzyi-7kAQ6-jjapV+GU-Mo0Q@mail.gmail.com>
	<54380F1F.1080309@fuzzy.cz>
	<CAFwQ8renpGVZPorJn4j7O7L_dGLaLqsmccR+7EhGxF0fvDgyTg@mail.gmail.com>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

On 10.10.2014 19:59, Craig James wrote:
> On Fri, Oct 10, 2014 at 9:53 AM, Tomas Vondra <tv@fuzzy.cz
> <mailto:tv@fuzzy.cz>> wrote:
> 
> 
>     On 10.10.2014 16:21, Craig James wrote:
>     > Our index is for chemical structures. Chemicals are indexed on
>     > chemical fragments
>     > <http://emolecules.com/info/molecular-informatics;. A search
>     > typically starts with 50-200 indexed "columns" (chemical fragments).
>     > The query is always flat, "A and B and ... and Z". The indexed
>     > fragments are both correlated (the existence of one strongly raises
>     > the chances of another) and anti-correlated (certain combinations are
>     > very rare).
> 
>     Maybe I don't understand the problem well enough, but isn't this a
>     perfect match for GIN indexes? I mean, you essentially need to do
>     queries like "WHERE substance @@ ('A & B & !C')" etc. Which is exactly
>     what GIN does, because it keeps pointers to tuples for each fragment.
> 
> 
> On the day our web site opened we were using tsearch. Before the end of
> the day we realized it was a bad idea, for the very reasons discussed
> here. The early-abort/late-start problem ("offset N limit M") could take
> minutes to return the requested page. With the external
> dynamically-optimized index, we can almost always get answers in less
> than a couple seconds, often in 0.1 seconds.

In the early days of tsearch, it did not support GIN indexes, and AFAIK
GiST are not nearly as fast for such queries. Also, the GIN fastscan
implemented by Alexander Korotkov in 9.4 makes a huge difference for
queries combining frequent and rare terms.

Maybe it'd be interesting to try this on 9.4. I'm not saying it will
make it faster than the optimized index, but it might be an interesting
comparison.

Tomas


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



view thread (60+ messages)  latest in thread

Message-ID: <543821C4.3030301@fuzzy.cz>
Permalink:  ../543821C4.3030301@fuzzy.cz/
Also on:    postgresql.org/message-id/543821C4.3030301@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-performance@postgresql.org
  Cc: tv@fuzzy.cz
  Subject: Re: Yet another abort-early plan disaster on 9.3
  In-Reply-To: <543821C4.3030301@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