Bingo substructure search issues on postgresql DB for kekulized compounds
- 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