0

I'm new to bloom indexes and currently my table has 3 partial indexes for the same column in the WHERE clause and with different column combinations for the same table. I was trying to replace them all with 1 bloom index.

However, when I create a bloom index with WHERE clause using below syntax, I get below error:

CREATE EXTENSION bloom;

CREATE INDEX idx_bloom_bar ON message USING bloom  
(s, p, c, m, e, id) 
WHERE (islatest = true)
WITH (length=56, col1=4, col2=4, col3=4, col4=4, col5=4, col6=4);
SQL Error [42601]: ERROR: syntax error at or near "WITH"
Position: 165

However, without WHERE clause, I'm able to create this index successfully.

Please let me know if we can use them as a replacement of multiple partial indexes or not to reduce number of indexes on my table.

2
  • jjanes clarified the syntax error. I am not convinced that the bloom index beats your current indexing, though. Can you provide your previous indexes and table definition, and rough details of write patterns and relevant cardinalities? Commented Jul 9, 2020 at 14:36
  • Thanks @ErwinBrandstetter , I can't provide those details here. However, I'm further working on performance analysis/observing changes into the query execution time (after replacing old indexes with bloom) - will share details regarding increase/decrease into overall query execution timings.
    – Shiwangini
    Commented Jul 9, 2020 at 16:09

1 Answer 1

2

The WITH clause comes before the WHERE clause in the CREATE INDEX syntax. You are not free to rearrange the order as you have attempted here.

This ordering is a little unintuitive to me, but that is how it works.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.