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 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.

  • 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? Jul 9 '20 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. Jul 9 '20 at 16:09

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, privacy policy and cookie policy

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