epam / epam/Indigo

Bingo substructure search issues on postgresql DB for kekulized compounds

Open
#3,055 0 comments 2 reactions 0 assignees View on GitHub
User`s Feedback
Dominant language
C++
Stars
406
Forks
134
Avg merge
2d 11h
Merged PRs (30d)
24

Description

**Summary**
Running a substructure search query on a kekulized substructure can lead to infinite execution time.

**Steps to Reproduce**
1. Use Indigo library (Bingo cartridge 1.32.0). Postgres 17.5.0.
2. Import the provided smiles file content in a temporary table `tmp_smiles_input`
3. Create a search table from the temporary table with both a smiles and a bingo_molecule column.
```sql
CREATE TABLE "test_table" AS SELECT idx idx, Bingo.SMILES(smiles) smiles, Bingo.CompactMolecule(smiles, false) bingo_mol FROM tmp_smiles_input
WHERE Bingo.CheckMolecule(smiles) IS NULL;
```
4. Build indexes for the smiles and bingo molecule columns.
```sql
create index bingo_smiles_idx on test_table using bingo_idx (smiles bingo.molecule)
;
create index bingo_mol_idx on test_table using bingo_idx (bingo_mol bingo.bmolecule)
;
```
5. Run the following substructure search:
```sql
SELECT
*
FROM test_table t
WHERE SMILES @ ('N(C1=CC=CC=C1)C1=CC=CC=C1', 'TAU')::bingo.sub
; ```

**Actual behavior**
The substructure search run forever (waited 20min+).

**Expected behavior**
Obtain results or an error.

**Environment details:**
- Dockerized postgres database : docker.io/bitnami/postgresql:17.5.0

**Attachments**

[rcsb_smiles.csv](https://github.com/user-attachments/files/21486188/rcsb_smiles.csv)

**Additional context**
The same query is giving results when using the bmolecule index:
```sql
SELECT
*
FROM test_table t
WHERE bingo_mol @ ('N(C1=CC=CC=C1)C1=CC=CC=C1', 'TAU')::bingo.sub
; -- 1m21s 235 results
```

The query on the aromatized substructure is much faster and give results using both indexes:
```sql
select
*
FROM test_table t
WHERE SMILES @ ('N(c1ccccc1)c1ccccc1', 'TAU')::bingo.sub
; -- 26s 230 results

select
*
FROM test_table t
WHERE bingo_mol @ ('N(c1ccccc1)c1ccccc1', 'TAU')::bingo.sub
; -- 12s 230 results
```

Contributor guide

No contributing guide indexed for this repository

Research direction

Start by reproducing the SQL workflow on PostgreSQL 17.5 with the attached rcsb_smiles.csv data and compare the smiles and bingo_molecule indexes. Investigate the substructure-search path for the kekulized query and verify that it returns results or an error instead of running indefinitely; compare against the working bmolecule and aromatized queries.

Written by the indexing model from the issue text.

Assessment

Tech stack
cpp, postgresql
Domain
databases, search
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.