Count number of occurrences for each attribute

I have the honor to program a plant filter search.
Means you can search plants meeting certain conditions (attributes).
But I need some advice for counting number of occurring attributes.

Over simplified records:

- id: 1; attributes: [color: [red, green], size: 10];
- id: 2; attributes: [color: [red], light: [moderate];

The attributes are varying!

The filter search functionality should provide a counter for each attribute:

color: red (2), green (1)
size: 10 (1)
light: moderate (1)

Now if you’ll select moderate - green & size will be (0), red (1).

Sadly PostgreSQL is getting to its limit with this requirement.
Counting attributes is too slow.
So I’m in search of an alternative approach. → Advice needed!

More details:

  • It concerns about 2,5 k records
  • Attributes of all records will have a size of “just” about 10 - 15 MB
  • The first most common filters will reduce the record amount just by about 20%
  • Update of records / attributes won’t be very frequent


Should I just cache a list of all records + attributes in for example ETS?
And than do the counting / filtering with some Enum operations?

Or are there any other good approaches I’m currently blind for?

1 Like

It sounds like you are trying to do faceted search. There are some tips in this article that might be helpful for you: Faceted navigation and search | Bun

It is focused on how to do it in Postgres. With 2.5K records I believe you should be able to make this fast just using Postgres.


Thanks Joey. Ok, it definitely sound like it’s called faceted search, what I’m trying to do.

The article is very promising. I myself would never have had the idea to solve this via full text search and ts_stat. I will definitely try this way. It will get interesting if some of my deep nested attributes are compatible with this concept. I will see…

Of course I’ll be happy if it works with just Postgres. Because the record set isn’t really that large.

So this works perfectly fine. Thanks a lot.

1 Like

I’m glad it works :). Thanks for letting me know. I have plans to try it in my own project but haven’t had the time yet.