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? .... > 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. > 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. 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. 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. Also, if you tune your system using benzene, you will be probably arrive at a place not optimal for more realistic queries. Cheers, Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
