On Wed, Jun 22, 2016 at 11:36 AM, Jeff Janes <jeff.janes@xxxxxxxxx> wrote:
On Wed, Jun 22, 2016 at 9:03 AM, Craig James <cjames@xxxxxxxxxxxxxx> 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 OfficerCraig A. James
