Re: Can't get two index scans

On Wed, Jun 22, 2016 at 11:36 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Wed, Jun 22, 2016 at 9:03 AM, Craig James <cjames(at)emolecules(dot)com>
> wrote:
> > I'm working with a third-party plugin that does chemistry.
>
>
> Out of personal/professional curiosity, which one are you using, if
> that can be disclosed?
>

ChemAxon (JChem)

> > Notice that it doesn't use the i_version_smiles index at all, but instead
> > applies the very expensive filter |>| to all 1% of the database.
>
> You have to tell the database that |>| is very expensive, by setting
> the COST of the function which it invokes. You can get the name of
> the function with:
>
> select oprcode from pg_operator where oprname ='|>|' ;
>
> (taking care for schema and overloading, etc.)
>
> I would set the COST to at least 1000, probably more.
>

I'll try this. I've done it with my own functions, but didn't realize you
could do it with existing operators.

> > So instead
> > of getting a 100x speedup, we only get a 3x speedup, about 30x worse that
> > what is theoretically possible.
> >
> > The production database is about 50x larger than this test database.
> >
> > Maybe I misunderstand what's possible with indexes, but it seems to me
> that
> > it could first do the pk_version index scan, and then use the results of
> > that to do a limited index-scan search using the i_version_smiles index.
> Is
> > that not possible?
>
> I don't think it can do that. What it can do is run each index scan
> to completion as a bitmap index scan, and then AND the bitmaps
> together.
>

That won't help in this case because the index scan of the molecule table
can be slow.

>
> You might be able to build a multiple column index on (smiles,
> version_id) and have it do the right thing automatically. Whether that
> is possible, and if so how effective it will actually be, would depend
> on the implementation details of |>|. My gut feeling is that it would
> not work well.
>

No, because it's not a normal exact-match query. The analogy would be that
you can build a multi-column index for an '=' operation on a string, but it
wouldn't help if you were doing an '~' or 'LIKE' operation.

> You could partition your data on version_id. Then it would keep a
> separate smiles index on each partition, and would only consult those
> indexes which can possibly contain (according to the CHECK
> constraints) the version_ids of interest in the query.
>

I actually struck on this solution today and it works well. Instead
partitioning on the version_id, I added a column "p" ("partition") and used
20 partitions where p is a random number from 0..19. This has the advantage
that as new compounds are added, they are distributed throughout the
partitions, so each partition remains a 5% sample of the whole.

It's pretty cool. A full-table scan of all partitions is slightly slower,
but if I want to do a sample and limit the run time, I can query with p = 0.

It also has another huge benefit for a web site: I can give the user a
progress-bar widget by querying the partitions one-by-one and updating the
progress in 5% increments. This is really critical for long-running
queries.

> Also, if you tune your system using benzene, you will be probably
> arrive at a place not optimal for more realistic queries.
>

No, it's actually very useful. I'm not interested in optimizing typical
queries, but rather in limiting worst-case queries. This is a public web
site, and you never know what molecule someone will draw. In fact, it's
quite common for visitors to draw silly molecules like benzine or methane
that would result in a heavy load if left to run to completion.

Thanks for your help!
Craig

> Cheers,
>
> Jeff
>

--
---------------------------------
Craig A. James
Chief Technology Officer
eMolecules, Inc.
---------------------------------